# 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
###### Who is Participating?

x

Database 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
``````

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

Database ExpertCommented:
Can you please tell me how you have calculated the hours, any example?
0

Author 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

Commented:
>>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

Author 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

Database ExpertCommented:
Hi Colin,
Did u try my query.?

Regards,
Pawan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.