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".
CodePudding user response：
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;
CodePudding user response：
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.
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...
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 |