Clever Geek Handbook

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

I am stuck. I have been looking for an answer but cannot find the subtraction time in the same table from 2 different rows of the same table that fits. I am having a difficult time with the following request. In the table below, I want to differentiate TimeOut from one row to TimeIn of the next row. In the following table, find the difference in minutes between TimeOut on line 1 (10:35 am) and TimeIn on line 2 (10:38).

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 time in the request to show how long employees have been on a break.

I tried making a connection, but it doesn't work and I don't know if there is a way OVER

with PARTITION

, because I can't follow the logic (Yes, I'm still learning). I also look at two temporary tables and compare them, but that doesn't work when I start changing days or employee IDs. Finally, I think maybe LEAD

in expression OVER

? Or just to do DATEDIFF

with CAST

?

+3


source to share


5 answers


I solved it for similar tasks, and it is not necessary that the strings 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 the sql fiddle: http://sqlfiddle.com/#!3/89a43/1

+2


source


try something like this:



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
+3


source


Since you mentioned the PARTITION clause below is the version using that clause (not checked for syntax, but it should give you an 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
+1


source


While looping through the cursor is usually a grim way of doing things both in terms of performance and provability and maintainability, in cases like this, when you cross the edges of a directed acyclic graph, the loop can be just what the doctor ordered ...

In my opinion, you have two good options when using SQL Server versions that do not support LEAD OVER PARTITION.

  • Cycle
  • using cursor in T-SQL
  • using LINQ, enumerated in the application code

Whether or not it is worth collecting all the vital LINQ support depends on what you are doing.

The question is whether sql-server-2008 is flagged which does not support LEAD OVER PARTITION, and on this platform in the absence of support indexes it is much faster when using a much smaller working set to use the cursor.

0


source


With AS strings

(

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

0


source






More articles:


All Articles