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
But I need to populate 7 for fisc_day_of_wk in first row and then 1,2,3 and so on.