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.

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.

Do you have any suggestion to calculate this? Thank you

CodePudding user response：

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.

CodePudding user response：

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;