Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 DATEDIFF in hours and minits In Decimal Format Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, October 3, 2013 6:09 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, May 5, 2016 5:20 AM Points: 114, Visits: 265
 Calculating Difference between ClockIn and ClockOut by Job.Table : JobTranJob# ClockIn ClockOut---------------------------------------------------------------------Job1 2013-10-03 06:17:34.387 NULLJob1 2013-10-03 05:17:34.387 2013-10-03 08:10:34.387Job2 2013-10-03 09:17:34.387 2013-10-03 10:05:34.387Job2 2013-10-03 09:17:34.387 NULLif the ClockOut is NULL I would use GETDATE() to calculate the difference between ClockIn and ClockOutAssuming GETDATE() is 2013-10-03 11:15:34.387Job1 - 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 3, 2013 7:30 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, October 3, 2016 5:19 AM Points: 1,144, Visits: 3,432
 `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 ClockTimeFROM JobTranGROUP BY JobNbr;` The SQL Guy @ blogspot@SeanPearceSQLAbout Me
Post #1501181

 Permissions