Subquery to compare duration? 2

[beckwiga]

Hi All!

I'm pretty sure this is possible, although I'm having trouble getting started. If you look at my screen in the link, I want to compare the time in the record with the time in the record just after it (log_id + 1) and then do a TIMEDIFF (if such a function exists to get the number of seconds elapsed from one operation to the next? Does anyone know how I can get started? I'm assuming I need to do a subquery to get the record after the record in my main query and then compare the times of the two.

BTW, I'm doing this in SQL Server syntax... Thanks in advance for any help.

beckwiga

 

[gmmastros]

What is the data type of the Log_Time column? This may cause problems with the code I present below.

Select A.Log_Id, B.Log_Id, 
       A.Log_Time, B.Log_Time,
       DateDiff(second, A.Log_Time, B.Log_Time) As Duration
From   YourTableName As A
       Inner Join YourTableName As B
         On A.Log_ID = B.Log_Id - 1

Try the query shown above. If this works for you, and you would like me to explain it, just let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

All you need for your problem is a self-join and datediff function.

I haven't checked your screen, but based on the description it would be something like (not tested):

select T1.Log_ID, T1.DateField, T2.ID, T2.DateField, datediff(second, T1.DateFiled, T2.DateField) as TimeDifference from myTable T1 LEFT JOIN myTable T2 on T1.Log_ID = T2.Log_ID - 1

 

[markros]

George,

It's not even funny - the same time and same solution

 

[gmmastros]

Except.... you used a left join and I used an inner join. This would affect the last row of data. Suppose there are 8 rows. Row 1 joins to row2, row2 joins to row 3. Row 8 wouldn't join to anything. An inner join would remove that row where a left join would keep it. Obviously this row wouldn't have a duration because there's nothing else to compare it to. So... if all you really care about is duration, then the inner joined version is probably the right one to use. If you return other data, then the left joined version may be better.

Either way... it is kinda funny.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes - I was thinking exactly what you put in...

 

[beckwiga]

You guys are ridiculously awesome. Thanks so much for the help. You both were right on. I went with the LEFT JOIN approach because I do need the last record of my survey to be returned in my result set even though it may have a NULL for duration. Here's my output...

Thanks guys! I'll give you both stars if that's possible.