Colin Hart
asked on
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
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
Pawan Kumar
Can you please tell me how you have calculated the hours, any example?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.)?
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.)?
ASKER
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
Hi Colin,
Did u try my query.?
Regards,
Pawan
Did u try my query.?
Regards,
Pawan