This function: fn_Week_Period_Act() was designed based on accounting (payroll) periods that start on a Saturday. This can be applied against a set of records like timesheets or checks. Then this information can be aggregated based on the accounting week (sat - fri) instead of the calendar week (sun - sat). The function is an inline table function with 1 (datetime) parameter. This can be called by itself as:
This can also be JOINed or CROSS APPLYed to an existing table as:
FROM dbo.TimeSheets AS ts
CROSS APPLY dbo.fn_Week_Period_Act(ut.Work_Date) AS wpa
Have a good day and enjoy.