Q: Expressions for a Calendar Date Table I have this Calendar Date table (see image below), but would like to learn the expressions behind a few of the columns.
The columns I need to replicate are the last three columns: "WeekBegin", "WeekEnd", "DaysinWeek". Of course, I really just need help with one to get the rest, so "WeekBegin" is the main question.
For dates 1/1/2023-1/3/2023, it gives the "WeekBegin" as 1/1/2023, then for 1/4/2023-1/10/2023 it gives the "WeekBegin" as 1/4/2022. This is perfect, I just need to shift it to a Monday Begin. So the result would be 1/1/2023 is "WeekBegin" 1/1/2023, while 1/2/2023-1/8/2023 is "WeekBegin" 1/2/2023.

I can't figure out the right code to have this replicate across the years! I've tried the regular date formulas, but can't figure out this dynamic piece to NOT have the week go into the previous year for "WeekBegin".

A: You could take either the previous wednesday or start of the year whichever happens later. Before we do the math, we need to add one day to date  (see dates+1 below) so that if today was a wednesday it would return today's date as beginning of the week
with cte(dates) as

(select current_date()-1 union all
 select current_date()-2 union all
 select current_date()-3 union all
 select current_date()-4 union all
 select current_date()-5 union all
 select current_date()-6 union all
 select current_date()-7 union all
 select current_date()-8 union all
 select current_date()-9 union all
 select current_date()-10 union all
 select current_date()-11 union all
 select current_date()-12 union all
 select current_date()-13 union all
 select current_date()-14 union all
 select current_date()-15 union all
 select current_date()-16 union all
 select current_date()-17 union all
 select current_date()-18 )

select *, greatest(previous_day(dates+1,'we'),trunc(dates,'month')) as week_begin
from cte
order by dates asc;


A: Before I start, a PSA about a couple of session parameters you will want to be aware of when working with dates. These will impact certain date functions.
If possible, I highly recommend setting these values account-wide for consistency. The defaults are ISO standard, but also, in my experience, the least likely to be used by (US-based) companies.
WEEK_START
Type: Session — Can be set for Account » User » Session
Data Type: Number
Description:
Specifies the first day of the week (used by week-related date functions).
Values:

*

*0: Legacy Snowflake behavior is used (i.e. ISO-like semantics).


*1 (Monday) to 7 (Sunday): All the week-related functions use weeks
that start on the specified day of the week.
Default:
0 (i.e. legacy Snowflake behavior)
and...
WEEK_OF_YEAR_POLICY
Type: Session — Can be set for Account » User » Session
Data Type: Number
Description: Specifies how the weeks in a given year are computed.
Values:

*

*0: The semantics used are equivalent to the ISO semantics, in which a week belongs to a given year if at least 4 days of that week are in that year.


*1: January 1 is included in the first week of the year and December 31 is included in the last week of the year.
Default: 0 (i.e. ISO-like behavior)
With that out of the way...
This expression should work to make sure the weekstart date, derived from a calendar date, starts on the first day of the year, if the weekstart returns as in the previous year.
iff(  -- is the year extracted from the derived weekstart date the same as the year extracted from the date?
      year(date_trunc(week, date_key)) = year(date_key)
      -- if so, truncate date to the start of the week
      , date_trunc(week, date_key) 
      -- if not, the fist day of the week must be the first day of the calendar year.
      , date_trunc(year, date_key) 
    ) dk_weekstart_calendar_year

sample query  w/ results across the first 10 days of a few years  (highlighed results are the standard datetrunc(week, ) values, desired results will to the right in the table).
with sample_dates as (
select 
    array_construct(
        '2019-01-01',
        '2019-01-02',
        '2019-01-03',
        '2019-01-04',
        '2019-01-05',
        '2019-01-06',
        '2019-01-07',
        '2019-01-08',
        '2019-01-09',
        '2020-01-01',
        '2020-01-02',
        '2020-01-03',
        '2020-01-04',
        '2020-01-05',
        '2020-01-06',
        '2020-01-07',
        '2020-01-08',
        '2020-01-09',
        '2021-01-01',
        '2021-01-02',
        '2021-01-03',
        '2021-01-04',
        '2021-01-05',
        '2021-01-06',
        '2021-01-07',
        '2021-01-08',
        '2021-01-09',
        '2022-01-01',
        '2022-01-02',
        '2022-01-03',
        '2022-01-04',
        '2022-01-05',
        '2022-01-06',
        '2022-01-07',
        '2022-01-08',
        '2022-01-09',
        '2023-01-01',
        '2023-01-02',
        '2023-01-03',
        '2023-01-04',
        '2023-01-05',
        '2023-01-06',
        '2023-01-07',
        '2023-01-08',
        '2023-01-09'
    ) as date_array
)
select
      f.value::date as date_key
     , dayname(date_key) as dk_name
     , dayofweek(date_key) as dk_dayofweek
     , date_trunc(week, date_key) as dk_weekstart -- always, first day of the week as defined by week_start, *will go to previous year*
     , dayname(dk_weekstart) as dk_weekstart_name -- 
     , iff(
           year(date_trunc(week, date_key)) = year(date_key) -- is the year extracted from the date of the weekstart the same as the year of date?
         , date_trunc(week, date_key) -- if so, truncate date to the start of the week
         , date_trunc(year, date_key) -- if not, the fist day of the week must be the first day of the calendar year.
     ) dk_weekstart_calendar_year
     , dayname(dk_weekstart_calendar_year) as dk_weekstart_calendar_year_name
     , last_day(date_key, 'week') as dk_weekend
     , dayname(dk_weekend) as dk_weekend_name
from sample_dates e,
lateral flatten(e.date_array) f;





DATE_KEY
DK_NAME
DK_DAYOFWEEK
DK_WEEKSTART
DK_WEEKSTART_NAME
DK_WEEKSTART_CALENDAR_YEAR
DK_WEEKSTART_CALENDAR_YEAR_NAME
DK_WEEKEND
DK_WEEKEND_NAME




2019-01-01
Tue
2
2018-12-31
Mon
2019-01-01
Tue
2019-01-06
Sun


2019-01-02
Wed
3
2018-12-31
Mon
2019-01-01
Tue
2019-01-06
Sun


2019-01-03
Thu
4
2018-12-31
Mon
2019-01-01
Tue
2019-01-06
Sun


2019-01-04
Fri
5
2018-12-31
Mon
2019-01-01
Tue
2019-01-06
Sun


2019-01-05
Sat
6
2018-12-31
Mon
2019-01-01
Tue
2019-01-06
Sun


2019-01-06
Sun
7
2018-12-31
Mon
2019-01-01
Tue
2019-01-06
Sun


2019-01-07
Mon
1
2019-01-07
Mon
2019-01-07
Mon
2019-01-13
Sun


2019-01-08
Tue
2
2019-01-07
Mon
2019-01-07
Mon
2019-01-13
Sun


2019-01-09
Wed
3
2019-01-07
Mon
2019-01-07
Mon
2019-01-13
Sun


2020-01-01
Wed
3
2019-12-30
Mon
2020-01-01
Wed
2020-01-05
Sun


2020-01-02
Thu
4
2019-12-30
Mon
2020-01-01
Wed
2020-01-05
Sun


2020-01-03
Fri
5
2019-12-30
Mon
2020-01-01
Wed
2020-01-05
Sun


2020-01-04
Sat
6
2019-12-30
Mon
2020-01-01
Wed
2020-01-05
Sun


2020-01-05
Sun
7
2019-12-30
Mon
2020-01-01
Wed
2020-01-05
Sun


2020-01-06
Mon
1
2020-01-06
Mon
2020-01-06
Mon
2020-01-12
Sun


2020-01-07
Tue
2
2020-01-06
Mon
2020-01-06
Mon
2020-01-12
Sun


2020-01-08
Wed
3
2020-01-06
Mon
2020-01-06
Mon
2020-01-12
Sun


2020-01-09
Thu
4
2020-01-06
Mon
2020-01-06
Mon
2020-01-12
Sun


2021-01-01
Fri
5
2020-12-28
Mon
2021-01-01
Fri
2021-01-03
Sun


2021-01-02
Sat
6
2020-12-28
Mon
2021-01-01
Fri
2021-01-03
Sun


2021-01-03
Sun
7
2020-12-28
Mon
2021-01-01
Fri
2021-01-03
Sun


2021-01-04
Mon
1
2021-01-04
Mon
2021-01-04
Mon
2021-01-10
Sun


2021-01-05
Tue
2
2021-01-04
Mon
2021-01-04
Mon
2021-01-10
Sun


2021-01-06
Wed
3
2021-01-04
Mon
2021-01-04
Mon
2021-01-10
Sun


2021-01-07
Thu
4
2021-01-04
Mon
2021-01-04
Mon
2021-01-10
Sun


2021-01-08
Fri
5
2021-01-04
Mon
2021-01-04
Mon
2021-01-10
Sun


2021-01-09
Sat
6
2021-01-04
Mon
2021-01-04
Mon
2021-01-10
Sun


2022-01-01
Sat
6
2021-12-27
Mon
2022-01-01
Sat
2022-01-02
Sun


2022-01-02
Sun
7
2021-12-27
Mon
2022-01-01
Sat
2022-01-02
Sun


2022-01-03
Mon
1
2022-01-03
Mon
2022-01-03
Mon
2022-01-09
Sun


2022-01-04
Tue
2
2022-01-03
Mon
2022-01-03
Mon
2022-01-09
Sun


2022-01-05
Wed
3
2022-01-03
Mon
2022-01-03
Mon
2022-01-09
Sun


2022-01-06
Thu
4
2022-01-03
Mon
2022-01-03
Mon
2022-01-09
Sun


2022-01-07
Fri
5
2022-01-03
Mon
2022-01-03
Mon
2022-01-09
Sun


2022-01-08
Sat
6
2022-01-03
Mon
2022-01-03
Mon
2022-01-09
Sun


2022-01-09
Sun
7
2022-01-03
Mon
2022-01-03
Mon
2022-01-09
Sun


2023-01-01
Sun
7
2022-12-26
Mon
2023-01-01
Sun
2023-01-01
Sun


2023-01-02
Mon
1
2023-01-02
Mon
2023-01-02
Mon
2023-01-08
Sun


2023-01-03
Tue
2
2023-01-02
Mon
2023-01-02
Mon
2023-01-08
Sun


2023-01-04
Wed
3
2023-01-02
Mon
2023-01-02
Mon
2023-01-08
Sun


2023-01-05
Thu
4
2023-01-02
Mon
2023-01-02
Mon
2023-01-08
Sun


2023-01-06
Fri
5
2023-01-02
Mon
2023-01-02
Mon
2023-01-08
Sun


2023-01-07
Sat
6
2023-01-02
Mon
2023-01-02
Mon
2023-01-08
Sun


2023-01-08
Sun
7
2023-01-02
Mon
2023-01-02
Mon
2023-01-08
Sun


2023-01-09
Mon
1
2023-01-09
Mon
2023-01-09
Mon
2023-01-15
Sun



