home


Daily Hours Worked
Author Nigel Rivett
Input
start and end work times per day of employees
Employee may have many periods of work during the day
The period will not span days (easy to split up if needed though)

Output
For each employee
Total hours worked each day
Duration between start and end work times for that day (breaks?)
Total hours worked each week (overtime?)



declare @w table (pid varchar(20), d_strt datetime, d_end datetime)
insert @w select 'staff_1', '1 jan 2000 8:00', '1 jan 2000 17:00'
insert @w select 'staff_1', '2 jan 2000 8:00', '2 jan 2000 12:00'
insert @w select 'staff_1', '2 jan 2000 12:30', '2 jan 2000 15:00'
insert @w select 'staff_1', '2 jan 2000 15:30', '2 jan 2000 17:00'
insert @w select 'staff_1', '3 jan 2000 8:30', '3 jan 2000 17:00'
insert @w select 'staff_1', '4 jan 2000 8:30', '4 jan 2000 17:00'
insert @w select 'staff_1', '5 jan 2000 8:30', '5 jan 2000 17:00'
insert @w select 'staff_1', '6 jan 2000 8:30', '6 jan 2000 17:00'
insert @w select 'staff_1', '7 jan 2000 8:30', '7 jan 2000 17:00'
insert @w select 'staff_1', '8 jan 2000 8:30', '8 jan 2000 17:00'
insert @w select 'staff_1', '9 jan 2000 8:30', '9 jan 2000 17:00'
insert @w select 'staff_1', '10 jan 2000 8:30', '10 jan 2000 17:00'


declare @startdate datetime = '1 jan 2000'
declare @enddate datetime = '30 jan 2000'
declare @weekend datetime = '26 dec 1999'	-- Sunday


;with cte as
(
	select d = @startdate, weekno = datediff(dd, @weekend, @startdate-1)/7+1
	union all
	select d+1, weekno = datediff(dd, @weekend, d)/7+1 from cte where d < @enddate 
)
, cte1 as
(
	select	staff.pid, 
			date = d.d, 
			daily_hours = coalesce(sum(datediff(mi,t.d_strt,t.d_end))/60.0,0), 
			daily_period = coalesce(datediff(mi,min(t.d_strt),max(t.d_end))/60.0,0),
			WeekNo = max(d.weekno)
	from cte d
		cross join (select distinct pid from @w) staff
		left join @w t
			on t.d_strt between d.d and d.d+1
			and t.pid = staff.pid
	group by staff.pid, d.d
)
select t1.pid, t1.date, t1.daily_hours, t1.daily_period, t1.WeekNo ,
	weeksum = sum(t2.daily_hours),
	DayName = datename(dw,t1.date)
from cte1 t1
	join cte1 t2
		on t1.pid = t2.pid
		and t1.WeekNo = t2.WeekNo
		and t2.date <= t1.date
group by t1.pid, t1.date, t1.daily_hours, t1.daily_period, t1.WeekNo
order by pid, date


Result
pid                  date                    daily_hours                             daily_period                            WeekNo      weeksum                                 DayName
-------------------- ----------------------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- ------------------------------
staff_1              2000-01-01 00:00:00.000 9.000000                                9.000000                                1           9.000000                                Saturday
staff_1              2000-01-02 00:00:00.000 8.000000                                9.000000                                1           17.000000                               Sunday
staff_1              2000-01-03 00:00:00.000 8.500000                                8.500000                                2           8.500000                                Monday
staff_1              2000-01-04 00:00:00.000 8.500000                                8.500000                                2           17.000000                               Tuesday
staff_1              2000-01-05 00:00:00.000 8.500000                                8.500000                                2           25.500000                               Wednesday
staff_1              2000-01-06 00:00:00.000 8.500000                                8.500000                                2           34.000000                               Thursday
staff_1              2000-01-07 00:00:00.000 8.500000                                8.500000                                2           42.500000                               Friday
staff_1              2000-01-08 00:00:00.000 8.500000                                8.500000                                2           51.000000                               Saturday
staff_1              2000-01-09 00:00:00.000 8.500000                                8.500000                                2           59.500000                               Sunday
staff_1              2000-01-10 00:00:00.000 8.500000                                8.500000                                3           8.500000                                Monday
staff_1              2000-01-11 00:00:00.000 0.000000                                0.000000                                3           8.500000                                Tuesday
staff_1              2000-01-12 00:00:00.000 0.000000                                0.000000                                3           8.500000                                Wednesday
staff_1              2000-01-13 00:00:00.000 0.000000                                0.000000                                3           8.500000                                Thursday
staff_1              2000-01-14 00:00:00.000 0.000000                                0.000000                                3           8.500000                                Friday
staff_1              2000-01-15 00:00:00.000 0.000000                                0.000000                                3           8.500000                                Saturday
staff_1              2000-01-16 00:00:00.000 0.000000                                0.000000                                3           8.500000                                Sunday
staff_1              2000-01-17 00:00:00.000 0.000000                                0.000000                                4           0.000000                                Monday
staff_1              2000-01-18 00:00:00.000 0.000000                                0.000000                                4           0.000000                                Tuesday
staff_1              2000-01-19 00:00:00.000 0.000000                                0.000000                                4           0.000000                                Wednesday
staff_1              2000-01-20 00:00:00.000 0.000000                                0.000000                                4           0.000000                                Thursday
staff_1              2000-01-21 00:00:00.000 0.000000                                0.000000                                4           0.000000                                Friday
staff_1              2000-01-22 00:00:00.000 0.000000                                0.000000                                4           0.000000                                Saturday
staff_1              2000-01-23 00:00:00.000 0.000000                                0.000000                                4           0.000000                                Sunday
staff_1              2000-01-24 00:00:00.000 0.000000                                0.000000                                5           0.000000                                Monday
staff_1              2000-01-25 00:00:00.000 0.000000                                0.000000                                5           0.000000                                Tuesday
staff_1              2000-01-26 00:00:00.000 0.000000                                0.000000                                5           0.000000                                Wednesday
staff_1              2000-01-27 00:00:00.000 0.000000                                0.000000                                5           0.000000                                Thursday
staff_1              2000-01-28 00:00:00.000 0.000000                                0.000000                                5           0.000000                                Friday
staff_1              2000-01-29 00:00:00.000 0.000000                                0.000000                                5           0.000000                                Saturday
staff_1              2000-01-30 00:00:00.000 0.000000                                0.000000                                5           0.000000                                Sunday




home