October 3, 2013 at 6:09 am
Calculating Difference between ClockIn and ClockOut by Job.
Table : JobTran
Job# ClockIn ClockOut
---------------------------------------------------------------------
Job1 2013-10-03 06:17:34.387 NULL
Job1 2013-10-03 05:17:34.387 2013-10-03 08:10:34.387
Job2 2013-10-03 09:17:34.387 2013-10-03 10:05:34.387
Job2 2013-10-03 09:17:34.387 NULL
if the ClockOut is NULL I would use GETDATE() to calculate the difference between ClockIn and ClockOut
Assuming GETDATE() is 2013-10-03 11:15:34.387
Job1 - 7.92 total hours (4.96 from 1st record, 2.96 from second record )
Job2 - 2.76 total hours (0.8 from 1st record, 1.96 from second record )
October 3, 2013 at 7:30 am
CREATE TABLE JobTran
(JobNbr VARCHAR(10),
ClockIn DATETIME NOT NULL,
ClockOut DATETIME NULL);
INSERT INTO JobTran (JobNbr, ClockIn, ClockOut)
VALUES('Job1', '2013-10-03 06:17:34.387', NULL),
('Job1', '2013-10-03 05:17:34.387', '2013-10-03 08:10:34.387'),
('Job2', '2013-10-03 09:17:34.387', '2013-10-03 10:05:34.387'),
('Job2', '2013-10-03 09:17:34.387', NULL);
DECLARE @getdate-2 DATETIME = GETDATE();
/* For your Test */ SELECT @getdate-2 = '2013-10-03 11:15:34.387';
SELECT
JobNbr,
SUM(DATEDIFF(SECOND, ClockIn, ISNULL(ClockOut, @getdate-2)) / 60. / 60.) AS ClockTime
FROM
JobTran
GROUP BY
JobNbr;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply