Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# total hours between two times grouped by type

Posted on 2016-11-17
Medium Priority
84 Views
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
Question by:Colin Hart
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 32

Expert Comment

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

Author Comment

ID: 41892243
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

LVL 32

Accepted Solution

Pawan Kumar earned 2000 total points
ID: 41892516
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

LVL 32

Expert Comment

ID: 41893420
>>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 Comment

ID: 41893433
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

LVL 32

Expert Comment

ID: 41893855
Hi Colin,
Did u try my query.?

Regards,
Pawan
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
###### Suggested Courses
Course of the Month9 days, 14 hours left to enroll