mardi 30 septembre 2014

Find the time difference between two consecutive rows in the same table in sql


Vote count:

0




Let me tell ya, so much help here, my skill set is rapidly increasing. Thank you all for your help!!


MSSMS 2008 R2 Database is SQL 2008 SP1


However, I'm stuck. I've looked for an answer, but can't seem to find subtracting time in the same table from two different rows of the same table that fits. I'm having a difficult time with the following query. In the table below, I want to differentiate the TimeOut from one row to the TimeIn of the next row. Consider in the following table of finding the difference in minutes between the TimeOut in Row 1 (10:35am) and the TimeIn in Row 2 (10:38am).


Table 1: TIMESHEET



ROW EmpID TimeIn TimeOut
1 138 2014-01-05 10:04:00 2014-01-05 10:35:00
2 138 2014-01-05 10:38:00 2014-01-05 10:59:00
3 138 2014-01-05 11:05:00 2014-01-05 11:30:00


Expected results



ROW EmpID TimeIn TimeOut Minutes
1 138 2014-01-05 10:04:00 2014-01-05 10:35:00
2 138 2014-01-05 10:38:00 2014-01-05 10:59:00 3
3 138 2014-01-05 11:05:00 2014-01-05 11:30:00 6
etc
etc
etc


Basically, I need to differentiate the times in the query to show how long employees where on break. I've tried doing a join, but that doesn't seem to work and I don't know if OVER with PARTITION is the way to go, because I cannot seem to follow the logic (Yeah, I'm still learning). I also considering two temp tables and comparing them, but that doesn't work when I start changing days or employee ID's. Finally, I am thinking maybe LEAD in an OVER statement? Or is it just simple to do a DATEDIFF with a CAST?


Any help would be greatly appreciated!! Thanks in advance.



asked 1 min ago







Find the time difference between two consecutive rows in the same table in sql

Aucun commentaire:

Enregistrer un commentaire