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