Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DATEDIFF in hours and minits In Decimal Format Expand / Collapse
Author
Message
Posted Thursday, October 03, 2013 6:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, February 14, 2014 1:15 PM
Points: 86, Visits: 196
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 )
Post #1501141
Posted Thursday, October 03, 2013 7:30 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:49 AM
Points: 739, Visits: 2,470
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 DATETIME = GETDATE();

/* For your Test */ SELECT @GetDate = '2013-10-03 11:15:34.387';

SELECT
JobNbr,
SUM(DATEDIFF(SECOND, ClockIn, ISNULL(ClockOut, @GetDate)) / 60. / 60.) AS ClockTime
FROM
JobTran
GROUP BY
JobNbr;





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1501181
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse