How to calculate fisc_day_of_wk where my fiscal year starts 12/26/2020, Saturday?

58 Views Asked by At

I am using the below query in Redshift:

select 
  cast(NEW_SEQ AS INTEGER) as calndr_key
, date (datum)    as calndr_dt
, CAST(to_char(datum, 'ID') AS INTEGER ) as fisc_day_of_wk
, CAST(to_char(datum, 'DD') AS INTEGER ) as fisc_day_of_mth
, CAST(to_char(datum, 'DDD') AS INTEGER ) as fisc_day_of_yr
, CAST(to_char(datum, 'WW') AS INTEGER ) as fisc_wk
, CAST(to_char(datum, 'MM') AS INTEGER ) as fisc_mth
, CAST(to_char(datum, 'YYYYMM') AS INTEGER) as fisc_mth_num
, date (DATEADD(D, - (cast (DATE_PART(WEEKDAY,datum+2 - INTERVAL '1 DAY') as smallint)), datum+2))-2 as fisc_wk_strt_dt
, date (DATEADD(D,( 4 - (cast (DATE_PART(WEEKDAY, datum+2 - INTERVAL '1 DAY') as smallint))), datum+2)) as fisc_wk_end_dt
, date (date_trunc('Month', datum)) as fisc_mth_strt_dt
, last_day(datum) as fisc_mth_end_dt
   FROM
(    SELECT DATEADD('day', SEQ, MAX_DATE) AS datum
                ,(MAX_SEQ + SEQ)  AS NEW_SEQ
   FROM (SELECT ROW_NUMBER() OVER () AS SEQ FROM ads_devl.dim_calndr LIMIT 365) n,
   (SELECT MAX(calndr_key) AS MAX_SEQ FROM ads_devl.dim_calndr) ,
   (SELECT MAX(calndr_DT) AS MAX_DATE FROM ads_devl.dim_calndr where calndr_yr < '2021')
  ORDER BY 1)

enter code here

enter image description here

But I need to populate 7 for fisc_day_of_wk in first row and then 1,2,3 and so on.

0

There are 0 best solutions below