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

total hours between two times grouped by type

I have a dataset that looks like this


Employee Name         EmployeeType               StartDateTime           EndDateTime
Jane Smith                      Nurse                      2016-09-01 06:00:00     2016-09-01 14:00:00
Joe Smith                          Nurse                      2016-09-01 13:00:00     2016-09-02 09:00:00
Tom Jones                      Doctor                     2016-09-01 09:00:00     2016-09-01 13:00:00
George                           Doctor                     2016-09-01 20:00:00     2016-09-02 08:00:00

we have a rule that says my days are from 7am until 6:59am the following day so I need
to write a query that would sum up the hours worked on 9/1 (hours would be 2016-09-01 07:00:00 tp 2016-09-02 06:59:99)
and group them by Employee Type
so based on the data above my results should be

Employee Type            WorkDate      Hours
Nurse                  9/1/16            21  
Nurse                  9/2/16            2
Doctor                  9/1/16            15
Doctor                  9/2/16            1
0
Colin Hart
Asked:
Colin Hart
  • 3
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Can you please tell me how you have calculated the hours, any example?
0
 
Colin HartAuthor Commented:
our work day is from 7am until 6:59am the following day
so if I clock in at 6am one morning, my first hour (from 6am - 7am) will actually count for the prior day.

workdate for
9/1/16 would be between 2016/09/01 7:00:00 and 2016/09/02 6:59:99
9/2/16 would be between 2016/09/02 7:00:00 and 2016/09/03 6:59:99


in my example
Jane Smith = she would have 1 hours that goes to 8/31, 9 hours for 9/1
Joe Smith = would have 18 hours that go to 9/1 and 2 hours that go to 9/2
Tom Jones = would have 4 hours for 9/1
George Black = 11 hours for 9/1 and 1 hour for 9/2

sorry, my sum in the original example was wrong
should be
Employee Type            WorkDate      Hours
Nurse                             8/31/16         1
Nurse                            9/1/16            27
Nurse                          9/2/16            2
Doctor                          9/1/16            15
Doctor                           9/2/16            1
0
 
Pawan KumarDatabase ExpertCommented:
Try..

I think it should be Nurse   2016-09-01 25 instead of 27, please check.

DECLARE @StartDate AS DATETIME = '2016-01-01'
;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE0 AS
(
	SELECT *, DATEADD(d,Number,@StartDate) st FROM Series 
)
,CTE1
AS
(
	SELECT * , CASE WHEN StartDateTime > st then CAST(st AS DATE) else CAST(DATEADD(d,-1,CAST(StartDateTime AS DATE)) AS DATE) END starts
		     , CASE WHEN EndDateTime > et then CAST(et AS DATE) else CAST(DATEADD(d,-1,CAST(EndDateTime AS DATE)) AS DATE) END Ends
	FROM
	(
		SELECT EmployeeType,StartDateTime , EndDateTime , DATEADD(day, DATEDIFF(day, 0, CAST(StartDateTime AS DATE)), '07:00:00') st
		, DATEADD(day, DATEDIFF(day, 0, CAST(EndDateTime AS DATE)), '07:00:00') et
		FROM Employees
	)k
)
SELECT EmployeeType, CAST(b.st AS DATE) WorkDate
			,SUM(CASE WHEN starts = b.st THEN 
				CASE WHEN a.st > StartDateTime THEN DATEDIFF(hh,StartDateTime,a.st) ELSE 
					CASE WHEN starts <> Ends THEN
							DATEDIFF(hh,StartDateTime,CASE WHEN a.et < EndDateTime THEN a.et ELSE EndDateTime END) 
					ELSE
							DATEDIFF(hh,StartDateTime,EndDateTime)
					END
				END
			ELSE
				CASE WHEN a.et < EndDateTime THEN DATEDIFF(hh,a.et,EndDateTime) ELSE DATEDIFF(hh,a.StartDateTime,a.et)END  
			END) Hours 
FROM CTE1 a
INNER JOIN CTE0 b ON b.st BETWEEN a.starts AND a.Ends
GROUP BY EmployeeType,Number,CAST(b.st AS DATE)
ORDER BY EmployeeType DESC,Number

Open in new window


Output
----------------

EmployeeType      WorkDate      Hours
Nurse                     2016-08-31      1
Nurse                     2016-09-01      25
Nurse                     2016-09-02      2
Doctor                     2016-09-01      15
Doctor                     2016-09-02      1

Hope it helps !
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
awking00Commented:
>>I think it should be Nurse   2016-09-01 25 instead of 27, please check.<<

I do, too, but I also think it should be Doctor   2016-09-01 13 instead of 15 (10pm to 7am is 9 hours, not 11).
What dbms are you using (e.g. Sql Server, Oracle, etc.)?
0
 
Colin HartAuthor Commented:
you are correct Awking00, sorry as I am doing this from the hospital so my error checking is not at its best.  We are using MS Sql
0
 
Pawan KumarDatabase ExpertCommented:
Hi Colin,
Did u try my query.?

Regards,
Pawan
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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