Sum over defined Week limits

Question

I have a rolling date with associated hours. I want to sum the hours over my defined 7-day week range of Saturday to Friday.

So I need to define any date as a week beginning Saturday, week ending Friday and sum over this range.

The table is in the form of:

Date	Day	Hours
2021-06-12	Saturday	3
2021-06-18	Friday	3
2021-06-21	Monday	1
2021-06-22	Tuesday	2

Grouping the above table into Saturday-Friday Week sums.

Saturday	Friday	Total_Hours
2021-06-12	2021-06-18	6
2021-06-19	2021-06-25	3

Answer 1

You don't have to tie this to datepart() or datename(), which in turn depends on DATEFIRST (alas!).

Instead, you can use date arithmetic. This in turn depends on the fact that 0 date is a Monday. But that is not configurable (as far as I know).

So:

select v.week as saturday, dateadd(day, 6, v.week) as friday,
       sum(hours)
from t cross apply
     (values (dateadd(day, 7*datediff(week, 0, dateadd(day, 1, t.date)) - 2, 0))
     ) v(week)
group by v.week
order by v.week;

Here is a db<>fiddle.