Pravi Pravi - 3 months ago 18
SQL Question

calculation of lapsed time between two entry

I have table which records staff's work log. The table columns as staffid,orderid,Startingtime,endingtime etc.I know how to find the difference between two time ie starting and ending time. But I need to know how many minutes lapsing a worker to start another work.
staffid workid Startingtime endingtime timetaken
100 10222 11.30 pm 11.50 pm 20
100 20122 11.55 pm 01.55 pm 120
Here i need to calculate how many minutes staff lapsed between two work.(here 5 minutes 11.50pm and 11.55 pm)

Answer

Use the below query query for the desired result. Note : The query will work on a daily basis against all the staffs.

CREATE TABLE dbo.CalcTimeDiff
 (Staffid INT
  ,Orderid INT
  ,Startingtime DateTime
  ,Endingtime Datetime
 )
 GO


 INSERT INTO dbo.CalcTimeDiff
 SELECT '1','1','2016-08-21 11:30:00.000','2016-08-21 11:50:00.000'
 UNION
 SELECT '1','2','2016-08-21 11:55:00.000','2016-08-21 13:55:00.000'
 GO

Below query will give you the lapsed time in minute.

SELECT StaffiD,DATEDIFF(MINUTE,MIN(Startingtime),MAX(endingtime))-SUM(DATEDIFF(MINUTE,Startingtime,endingtime)) LapsedTime
FROM dbo.CalcTimeDiff
GROUP BY StaffiD,convert(date,Startingtime)
GO