Menu
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 were 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?
658
In the blue text, you can see the calculation of the SQL delta between two rows. To calculate a difference, you need a pair of records; those two records are “the current record” and “the previous year's record”. You obtain this record using the LAG() window function.
Example 1: Comparing rows of the same table. In the example, we are comparing the immediate rows to calculate the sales made on a day by comparing the amounts of two consecutive days. Syntax for inner join : SELECT column_name(s) FROM table1 t1 INNER JOIN table1 t2 on t1. column1 = t2.
To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond , second , minute , hour , day , week , month , quarter , or year .
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
try something like that:
select *, DATEDIFF(minute, (
select max(b.TimeOut)
from TIMESHEET as b where a.EmpID=b.EmpID and b.ROW<a.ROW
), a.TimeIn
) as diff
from TIMESHEET as a
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
