• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 124
  • Last Modified:

get hours worked between two datetime fields

I have a table of hours worked and I need to calculate the time worked between two periods

table
EmployeeID               StartDateTime                 EndDateTime
100                               09/01/2016 08:00:00    09/01/2016 16:45:00
101                               09/01/2016 15:15:00    09/02/2016 18:00:00
102                               09/01/2016 11:00:00    09/01/2016 18:45:00

I need to write a query that will pull back the number of hours worked for all employees between say
on 9/1/16 between the times of 10am to 1pm
so that would be a total of 5 hours
emp 100 has 3 hours
emp 101 has 0 hours
emp 102 has 2 hours

who would I write that?
0
Colin Hart
Asked:
Colin Hart
2 Solutions
 
Scott PletcherSenior DBACommented:
Something like this:

DECLARE @StartDateTime datetime
DECLARE @EndDateTime datetime

SET @StartDateTime = '20160901 10:00'
SET @EndDateTime = '20160901 13:00'

SELECT CAST(SUM(DATEDIFF(MINUTE,
    CASE WHEN StartDateTime < @StartDateTime THEN @StartDateTime ELSE StartDateTime END,
    CASE WHEN EndDateTime > @EndDateTime THEN @EndDateTime ELSE EndDateTime END))
        / 60.0 AS decimal(9, 2)) AS Total_Hours_Worked
FROM table_name
WHERE StartDateTime <= @EndDateTime AND EndDateTime > @StartDateTime
0
 
Pawan KumarDatabase ExpertCommented:
Complete code..Enjoy !!

--


CREATE TABLE testDt
(
	 EmployeeID INT              
	,StartDateTime DATETIME                
	,EndDateTime DATETIME
)
GO

INSERT INTO testDt VALUES
(100                               ,'09/01/2016 08:00:00'    ,'09/01/2016 16:45:00'),
(101                               ,'09/01/2016 15:15:00'    ,'09/02/2016 18:00:00'),
(102                               ,'09/01/2016 11:00:00'    ,'09/01/2016 18:45:00')
GO


DECLARE @Date AS DATE = '2016-09-01'
DECLARE @StartTime AS TIME = '10:00'
DECLARE @EndTime AS TIME = '13:00'

SELECT CONCAT(  'Emp ', EmployeeID , ' has '  ,
		  CASE WHEN 
			CAST((DATEDIFF(MINUTE, 
			CASE WHEN StartDateTime < st THEN st ELSE StartDateTime END,
			CASE WHEN EndDateTime > et THEN et ELSE EndDateTime END))
        / 60.0 AS INT) <= 0 THEN 0 ELSE 
		CAST((DATEDIFF(MINUTE, 
			CASE WHEN StartDateTime < st THEN st ELSE StartDateTime END,
			CASE WHEN EndDateTime > et THEN et ELSE EndDateTime END))
        / 60.0 AS INT) END , ' Hours' ) Output				
FROM
(
	SELECT EmployeeID , StartDateTime , EndDateTime , DATEDIFF(dd, 0,@Date) + CONVERT(DATETIME,@StartTime) st , DATEDIFF(dd, 0,@Date) + CONVERT(DATETIME,@EndTime) et
	FROM testDt t
)r

--

Open in new window


Ouptut....

Emp 100 has 3 Hours
Emp 101 has 0 Hours
Emp 102 has 2 Hours

:)
0
 
Colin HartAuthor Commented:
Sorry for the delay, I honestly thought I had put in my comments a while back.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now