Solved

Split hours worked between 2 times into shift buckets

Posted on 2011-03-24
3
661 Views
Last Modified: 2012-08-13
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
Comment
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
  • Learn & ask questions
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 450 total points
ID: 35211370
Please try the following:

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')
insert into #shifts values('S', '03:00PM',DATEADD(day,1,'12:00AM'))

select EmployeeName,
       CAST(ClockIn as datetime) ClockIn,
       CAST(ClockOut as datetime) ClockOut
  into #temp
  from YourHoursWorkedTable
  
update #temp
       set ClockOut = DATEADD(day,1,ClockOut)
  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

Open in new window

0
 

Author Comment

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

by:wdosanjos
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', '11:00PM',DATEADD(day,1,'07:00AM'))
insert into #shifts values('T', '12:00AM','07:00AM')
insert into #shifts values('F', '07:00AM','03:00PM')
insert into #shifts values('S', '03:00PM',DATEADD(day,1,'12:00AM'))

select EmployeeName,
       CAST(ClockIn as datetime) ClockIn,
       CAST(ClockOut as datetime) ClockOut
  into #temp
  from YourHoursWorkedTable
  
update #temp
       set ClockOut = DATEADD(day,1,ClockOut)
  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

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 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