How to construct date from (year, weekOfYear, dayOfWeek) in Snowflake?-CodePudding

I have a situation where using Snowflake, I need to construct a date from these parts (year, weekOfYear, dayOfWeek). ie (2022, 13th week, 3 for Weds) would equate to today's date 2022-03-30.

While this is possible in say Pandas, Snowflake's day_from_parts() does not seem to support this functionality. Is there some other method to construct a date from these 3 pieces of information?

For context, I am ultimately trying to create flags in my dimDate for 'weekToDate' and 'weekToDatePrevYear' (the prev year comparison is not actually the same date, but instead is Mon - today's dayOfWeek of the same weekOfYear, prev year). The flags must also account for weeks that span across two year's, and leap years with 53 weeks (in which case it will be compared to the 52nd week of prev year). The date I need to construct is the previous year's comparisonDate to today, from parts:

(year(current_date())-1, min(weekOfYear(current_date()), 52), dayOfWeek(current_date()))

Thanks in advance for any suggestions!

CodePudding user response：

Remember to set following parameter to ensure that, the first week of the year is the week that contains January 1st of that year.

ALTER SESSION SET WEEK_OF_YEAR_POLICY = 1;
 ---------------------------------- 
| status                           |
|----------------------------------|
| Statement executed successfully. |
 ----------------------------------

Generate the calender for whole year. Used sub-query from @Simeon Pilgrim

select * from
(
 select row_number() over (order by null)-1 as rn
        ,dateadd('day', rn, '2022-01-01'::date) as date_dt,WEEKOFYEAR(dateadd('day', rn, '2022-01-01'::date)
) as woy, DAYOFWEEK(dateadd('day', rn, '2022-01-01'::date)) dow
  from table(generator(rowcount=>365))
 )
 limit 10;
 ---- ------------ ----- ----- 
| RN | DATE_DT    | WOY | DOW |
|---- ------------ ----- -----|
|  0 | 2022-01-01 |   1 |   6 |
|  1 | 2022-01-02 |   1 |   0 |
|  2 | 2022-01-03 |   2 |   1 |
|  3 | 2022-01-04 |   2 |   2 |
|  4 | 2022-01-05 |   2 |   3 |
|  5 | 2022-01-06 |   2 |   4 |
|  6 | 2022-01-07 |   2 |   5 |
|  7 | 2022-01-08 |   2 |   6 |
|  8 | 2022-01-09 |   2 |   0 |
|  9 | 2022-01-10 |   3 |   1 |
 ---- ------------ ----- -----

Get the desired date based on day-of-week and week-of-year

select date_dt from
(
 select row_number() over (order by null)-1 as rn
        ,dateadd('day', rn, '2022-01-01'::date) as date_dt,WEEKOFYEAR(dateadd('day', rn, '2022-01-01'::date)
) as woy, DAYOFWEEK(dateadd('day', rn, '2022-01-01'::date)) dow
  from table(generator(rowcount=>365))
 )
 where woy=13 and dow=4;
 ------------ 
| DATE_DT    |
|------------|
| 2022-03-24 |
 ------------

Refer here for more information on extract date parts.

CodePudding user response：

@Panjak I was unable to get your code working... this piece "Get the desired date based on day-of-week and week-of-year" just got me today's date, previous year.

However I was able find another solution using a CTE! This is my final view with the WTD and WTD_PREV_YR flags. Note, it also contains MTD, MTD_PREV_YR, YTD, YTD_PREV_YR.

create or replace view DIM_DATE as (
    with comparison_date as (
        select
            case
                when weekofyear(current_date()) = 53
                then 52
                else weekofyear(current_date())
            end as comp_week
            , date as comp_date
            from <TABLE>
            where year(comp_date) = year(current_date()) - 1
            and weekofyear(comp_date) = comp_week
            and dayofweek(comp_date) = dayofweek(current_date())
    )
    select distinct
        d.date
        , case
            when weekofyear(date) = weekofyear(current_date())
            and date - current_date() <= 0
            and date - current_date() >= -6
            then 1
            else 0
        end as WTD
        , case
            when weekofyear(date) = weekofyear(c.comp_date)
            and date - c.comp_date <= 0
            and date - c.comp_date >= -6
            then 1
            else 0
        end as WTD_PREV_YR
        , CASE
            WHEN MONTH(DATE) = MONTH(CURRENT_DATE())
            AND DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE())
            THEN 1
            ELSE 0
        END AS MTD
        , CASE
            WHEN MONTH(DATE) = MONTH(CURRENT_DATE())
            AND DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE()) - 1
            THEN 1
            ELSE 0
        END AS MTD_PREV_YR
        , CASE
            WHEN DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE())
            THEN 1
            ELSE 0
        END AS YTD
        , CASE
            WHEN DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE()) - 1
            THEN 1
            ELSE 0
        END AS YTD_PREV_YR
    from <TABLE> as d
    join comparison_date as c on 1=1
    order by date desc
);