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)
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