How to get the time difference from dutyoff to dutyon of AM

We're stuck of this function on how to get the total hours of time column, what we want is, the duty off minus the time of duty on from morning time, as well as the afternoon time so that we can sum the hours of the time column. Dutyon and DutyOff are not column names, so we're having a hard time. Can you pleaaaaase help us. And we want to display the total to the last row of time column. Thanks.

Hey Ralph, this should help. Just sum the HoursWorked grouping by workersId on the final select.

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL

DROP TABLE #temptable

CREATE TABLE #temptable (

WorkersId INT,

InandOut VARCHAR(7),

dateandTime DATETIME2

)

INSERT INTO #temptable (WorkersId,InandOut,dateandTime) VALUES (111111113,'DutyOn','2016-01-20 08:00:00')

INSERT INTO #temptable (WorkersId,InandOut,dateandTime) VALUES (111111113,'DutyOff','2016-01-20 12:00:00')

INSERT INTO #temptable (WorkersId,InandOut,dateandTime) VALUES (111111113,'DutyOn','2016-01-20 13:00:00')

INSERT INTO #temptable (WorkersId,InandOut,dateandTime) VALUES (111111113,'DutyOff','2016-01-20 17:00:00')

;WITH cte AS (

--Data Setup

SELECT WorkersId, inandout, dateandtime,TimestampRanking, CASE WHEN inandout = 'DutyOn' THEN 1 ELSE 2 END AS Punch

FROM (

SELECT WorkersId, inandout, dateandtime, RANK() OVER(PARTITION BY WorkersId ORDER BY dateandTime) AS TimestampRanking

FROM #temptable

) A

)

SELECT PncOut.WorkersId,PncIn.dateandtime,PncOut.dateandtime,DATEDIFF(hh,PncIn.dateandtime,PncOut.dateandtime) AS HoursWorked

FROM cte PncOut

INNER JOIN cte PncIn ON PncOut.WorkersId = PncIn.WorkersId AND PncIn.punch = 1 AND PncOut.timeStampRanking = PncIn.timeStampRanking + 1

WHERE PncOut.punch = 2

Ken

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ralph Sacro10y ago
Thank you so much for the reply! :) hmmm.. But what we want is the timein and timeout is not directly specified. Hope you can help us. :) We really need your help.
0
10y ago
Hey Ralph, this should help. Just sum the HoursWorked grouping by workersId on the final select.

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL
DROP TABLE #temptable
CREATE TABLE #temptable (
WorkersId INT,
InandOut VARCHAR(7),
dateandTime DATETIME2
)
INSERT INTO #temptable (WorkersId,InandOut,dateandTime) VALUES (111111113,'DutyOn','2016-01-20 08:00:00')
INSERT INTO #temptable (WorkersId,InandOut,dateandTime) VALUES (111111113,'DutyOff','2016-01-20 12:00:00')
INSERT INTO #temptable (WorkersId,InandOut,dateandTime) VALUES (111111113,'DutyOn','2016-01-20 13:00:00')
INSERT INTO #temptable (WorkersId,InandOut,dateandTime) VALUES (111111113,'DutyOff','2016-01-20 17:00:00')
;WITH cte AS (
--Data Setup
SELECT WorkersId, inandout, dateandtime,TimestampRanking, CASE WHEN inandout = 'DutyOn' THEN 1 ELSE 2 END AS Punch
FROM (
SELECT WorkersId, inandout, dateandtime, RANK() OVER(PARTITION BY WorkersId ORDER BY dateandTime) AS TimestampRanking
FROM #temptable
) A
)
SELECT PncOut.WorkersId,PncIn.dateandtime,PncOut.dateandtime,DATEDIFF(hh,PncIn.dateandtime,PncOut.dateandtime) AS HoursWorked
FROM cte PncOut
INNER JOIN cte PncIn ON PncOut.WorkersId = PncIn.WorkersId AND PncIn.punch = 1 AND PncOut.timeStampRanking = PncIn.timeStampRanking + 1
WHERE PncOut.punch = 2

Ken
0