Solved

total hours between two times grouped by type

Posted on 2016-11-17
6
71 Views
Last Modified: 2016-11-22
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
Comment
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
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 29

Expert Comment

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

Author Comment

by:Colin Hart
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 29

Accepted Solution

by:
Pawan Kumar earned 500 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

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
Independent Software Vendors: 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!

 
LVL 32

Expert Comment

by:awking00
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

by:Colin Hart
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 29

Expert Comment

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

Regards,
Pawan
0

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question