Comparison of dates between lines

[JamesMF82]

Hi,

I am having some "fun" :banghead: at the moment trying to work out how to compare two times on different rows. The current setup I have is:

StartDTM EndDTM VisitID
09:00:00 10:00:00 1
11:00:00 12:00:00 2
12:15:00 13:00:00 3

etc etc...

I have found a SQL Query I have tried to use, however I am having no joy. The query is such:

WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY VisitID) AS rn
FROM Data
)
SELECT DATEDIFF("n", mc.EndDTM, mp.StartDTM)
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1

Could anyone guide me on what to do please / a better solution?

Many Thanks

James

 

[pr2-eugin]

Hello JamesMF82,

I am unable to understand the CODING (silly me ) could you please explain in simple terms?

You seem to have explained what you have, but I think you have failed to say What you want the result to be?

 

[JamesMF82]

..... yeah that would probably help wouldn't it sorry!

Basically I want the result to look like:

StartDTM EndDTM Difference VisitID
09:00:00 10:00:00 0 1
11:00:00 12:00:00 60 2
12:15:00 13:00:00 15 3

So I have an additional column which shows the difference between the current Start Time and previous End Time. For example, the start time of VisitID 2 - end time of VisitID 1.

Thanks

 

[redalert]

See attached file and run the qry_Results query.

Is this what you need?

 

[JamesMF82]

This is perfect! Thank you very much!

 

[redalert]

Just another technique to add to you library of knowledge.

 

[JamesMF82]

^True to that! Thanks again