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?
I have solved this for similar problems and it need not be that the rows even be sorted:
select t1.EmpID, t1.TimeIn, t1.TimeOut,
datediff(minute, max(t2.TimeOut), t1.TimeIn) as minutes
from timesheet t1 left join timesheet t2 on t1.EmpID = t2.EmpID
and t2.TimeOut < t1.TimeIn
group by t1.EmpID, t1.TimeIn, t1.TimeOut
Let me know if this works.
Here is a sql fiddle: http://sqlfiddle.com/#!3/89a43/1
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
Since you have mentioned PARTITION clause, given below is a version using that clause (haven't tested for syntax, but it should give you the idea)
;WITH EmpData AS
(
SELECT EmpID,
TimeIn,
TimeOut,
ROW_NUMBER() OVER(PARTITION BY EmpId ORDER BY TimeIn) Position
FROM EmployeeTime
)
SELECT a.*
a.TimeOut-b.TimeIn OutTIme
FROM EmpData a LEFT JOIN EmpData b
ON a.EmpId = b.EmpId
AND a.Position-1 = b.Position
WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY EmpID) AS rn
FROM TimeSheet
)
SELECT mc.EmpID, DATEDIFF(MINUTE, mc.TimeIn, mp.TimeOut) as TimeDiffInMinutes
FROM rows mc JOIN rows mp ON mc.rn = mp.rn-1
While looping through a cursor is normally a dismal way to do things both from a performance perspective and in terms of provability and maintainability, in cases like this, where you are traversing the edges of a directed acyclic graph, a loop can be just what the doctor ordered.
In my opinion you have two good options when using versions of SQL Server that do not support LEAD OVER PARTITION.
- loop using a cursor in T-SQL
- loop using a LINQ enumerable in application code
Whether it's worthwhile marshalling all the life-support for LINQ depends on what else you're doing.
The question is tagged sql-server-2008 which does not support LEAD OVER PARTITION, and on that platform in the absence of supporting indexes it is a great deal faster with a much smaller working set to use a cursor.
