Getting week of the month. Week should start on Monday and end a Sunday

Question

I need to add new business wee to my dim_date actually i have week of the month that is counting every 7 days of month as the table bellow.

Date	Week of month	day_of_week
01/04/2022	1	Friday
02/04/2022	1	Saturday
03/04/2022	1	Sunday
04/04/2022	1	Monday
05/04/2022	1	Tuesday
06/04/2022	1	Wednesday
07/04/2022	1	Thursday
08/04/2022	2	Friday
09/04/2022	2	Saturday
10/04/2022	2	Sunday

But for this business week column the week should start on Monday and end at Sunday, even if the first week is only one day(when a month starts at Sunday), but if the month starts on Friday, I should consider it as the first week of the month.

Date	Week of month	day_of_week
01/04/2022	1	Friday
02/04/2022	1	Saturday
03/04/2022	1	Sunday
04/04/2022	2	Monday
05/04/2022	2	Tuesday
06/04/2022	2	Wednesday
07/04/2022	2	Thursday
08/04/2022	2	Friday
09/04/2022	2	Saturday
10/04/2022	2	Sunday

Do you have any suggestion to calculate this? Thank you

Answer 1

This query gets the number of the week from 2002-04-01 to 2002-04-19. The week start from monday and finishes on sunday. You can see this example:

with sample_data as (
    SELECT date FROM UNNEST(generate_timestamp_array('2022-04-01', '2022-04-19', INTERVAL 1 DAY)) as date
)

select
    date 
    , extract(week(MONDAY) from  date)-(extract(MONTh from  date)-1)*4 AS days_diff
    ,  FORMAT_DATE('%A', date)
from sample_data

You can see the result.

You need to use EXTRACT function.

Answer 2

i hope that this solution i figured out could help you in the future or we can discuss and imporve this solution:

WITH sample_data AS (SELECT
                     date,
                     EXTRACT(WEEK FROM date) AS week_of_year,
                     DATE_TRUNC(date, MONTH) AS FIRST_DAY_OF_MONTH
                 FROM (SELECT date
                       FROM UNNEST(GENERATE_TIMESTAMP_ARRAY('2022-04-01', '2022-04-19', INTERVAL 1 DAY)) AS date))
SELECT
date,
FORMAT_DATE('%A', date) as day_name,
CASE
    WHEN CAST(EXTRACT(WEEK(MONDAY) FROM d.date) AS INT) = 0 AND d.WEEK_OF_YEAR IN (52, 53) THEN 1
    WHEN CAST(EXTRACT(WEEK(MONDAY) FROM d.FIRST_DAY_OF_MONTH) AS INT) = 0 THEN EXTRACT(WEEK(MONDAY) FROM d.date) + 1
    ELSE (d.WEEK_OF_YEAR - CAST(EXTRACT(WEEK(MONDAY) FROM d.FIRST_DAY_OF_MONTH) AS INT)) + 1
END AS BUSINESS_WEEK_OF_MONTH
FROM sample_data d;