home
 Donate to support site

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