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

x
Solved

Split hours worked between 2 times into shift buckets

Posted on 2011-03-24
Medium Priority
669 Views
I have a table of clock in times and clock out times which I need to separate into 3 shift buckets using sql 2000,  based on hours worked durring the shift. The shifts go from Midnight to 7AM (Third Shift), 7AM to 3PM (First Shift), and 3PM to Midnight (Second Shift). The shifts overlap so I need to know labor hours worked in each shift.
Here is an data example.

EmployeeName ClockIN ClockOut
Joe                     4:30AM 2:00PM
John                   1:00AM 8:00AM
Steve                  2:00PM 10:00PM

And the results would look like this

EmployeeName ThirdShift FirstShift SecondShift
Joe                         2.5           7                  0
John                        6              1                 0
Steve                       0              1                 7
0
Question by:fdorazio
[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
• 2

LVL 23

Accepted Solution

wdosanjos earned 1800 total points
ID: 35211370

``````create table #shifts (sname varchar(20), ShiftStart datetime, ShiftEnd datetime)
insert into #shifts values('T', '12:00AM','07:00AM')
insert into #shifts values('F', '07:00AM','03:00PM')

select EmployeeName,
CAST(ClockIn as datetime) ClockIn,
CAST(ClockOut as datetime) ClockOut
into #temp
from YourHoursWorkedTable

update #temp
where ClockOut < ClockIn

Select
EmployeeName,
SUM(case when sname = 'T' then HW else 0 end) ThirdShift,
SUM(case when sname = 'F' then HW else 0 end) FirstShift,
SUM(case when sname = 'S' then HW else 0 end) SecondShift
from
(
select e.EmployeeName, s.sname,
DATEDIFF(
minute,
case when e.ClockIn > s.ShiftStart then e.ClockIn else s.ShiftStart end,
case when e.ClockOut < s.ShiftEnd then e.ClockOut else s.ShiftEnd end) / 60.0 HW
from #temp e
inner join #shifts s on s.ShiftStart between e.ClockIn and e.ClockOut
or s.ShiftEnd between e.ClockIn and e.ClockOut
) hrs
Group By EmployeeName
Order By EmployeeName

drop table #temp
drop table #shifts
``````
0

Author Comment

ID: 35236406
The third shift actually starts on the day before from 11:00PM to 7:00AM. Instead of Midnight to 7:00AM. Is there a way to account for that?
0

LVL 23

Expert Comment

ID: 35243875
Does the third shift overlaps with the second?  Like this:

First:       7:00 AM -   3:00 PM
Second:  3:00 PM -   midnight
Third:    11:00 PM  -  7:00 AM

In that case if someone worked from 11:00 PM - 7:00 AM, how it should show on the report?

EmployeeName ClockIN    ClockOut
Joe                     11:00PM 7:00AM

EmployeeName ThirdShift FirstShift SecondShift
Joe                         8           0                  1

Anyway, the code below returns the results above:
``````create table #shifts (sname varchar(20), ShiftStart datetime, ShiftEnd datetime)
insert into #shifts values('T', '12:00AM','07:00AM')
insert into #shifts values('F', '07:00AM','03:00PM')

select EmployeeName,
CAST(ClockIn as datetime) ClockIn,
CAST(ClockOut as datetime) ClockOut
into #temp
from YourHoursWorkedTable

update #temp
where ClockOut < ClockIn

Select
EmployeeName,
SUM(case when sname = 'T' then HW else 0 end) ThirdShift,
SUM(case when sname = 'F' then HW else 0 end) FirstShift,
SUM(case when sname = 'S' then HW else 0 end) SecondShift
from
(
select e.EmployeeName, s.sname,
DATEDIFF(
minute,
case when e.ClockIn > s.ShiftStart then e.ClockIn else s.ShiftStart end,
case when e.ClockOut < s.ShiftEnd then e.ClockOut else s.ShiftEnd end) / 60.0 HW
from #temp e
inner join #shifts s on s.ShiftStart between e.ClockIn and e.ClockOut
or s.ShiftEnd between e.ClockIn and e.ClockOut
) hrs
Group By EmployeeName
Order By EmployeeName

drop table #temp
drop table #shifts
``````
0

Featured Post

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses
Course of the Month9 days, 4 hours left to enroll