sql group by end of the week on Sundays

I have 3 columns in my table:

bill_date (daily data), country, revenues

How to group by country and get sum of revenues if I want to group my bill_date by the end of every week that fall on Sundays ?

To be clearer for the current month ,November 2023 I would like to get following:

from day 1th November to sunday 5th November : week 1 ( 1 day of month, no matter which day it is , until 1th sunday of the month )
from 6 November to sunday 12th November : week 2 (week from monday to sunday)
from 13th November to sunday 19th November : week 3 (week from monday to sunday)
from 20th November to sunday 26th : week 4 (week from monday to sunday)
from 27th to thursday 30th : week 5 ( for the last week of every month, I would like to select all the days always starting from monday until the last day of the month, no matter which day will be the end of the month)

I have tried with week datepart function using SQL Server Management Studio 2018 version but with no success... I cannot see dateweek function in SQL Server Management Studio 2018 ...

Solution

if it can help, in Python, (and in general the algorithm) would be:

df['week'] = (
np.ceil(
(
df['DATE'].dt.day
- 7 +
df['DATE'].dt.to_period('M').dt.start_time.dt.dayofweek
)
/7)
).astype(int) + 1

you're using the first day of each month to establish when the first week of the month ends. example: first day is Monday. you'll have week calculated as ceil((1 - 7 + 0)/7) + 1 = 0 + 1 = 1 Tuesday: ceil((2 - 7 + 0)/7) + 1 = 0 + 1 = 1 ... the first next Monday: ceil((8 - 7 + 0)/7) + 1 = 1 + 1 = 2 and so on

if the first day of the week was Sunday, you'd get ceil((1-7+6)/7) + 1 = 0 + 1 = 1 but on the second day ceil((2-7+6)/7) + 1 = 1 + 1 = 2

the offset must equal the .dt.dayofweek of the first day of the month (because Monday = 0)