Further Evolved : The code below satisfies what was wanted in earlier comments [if you follow history] :), my new question right below is stumping me.
Question: I want to have 'Week_Of_Year' Adjust every Sunday, the code below does it every Monday.. I have tried 1 and 0 but via alter session per Snowflake but no luck! Any idea how to have a new week triggered by Sunday not Monday (as that is what it is currently)?
ie the result I want is for Date:1/2/2005 to reflect Week_Of_Year as 2 , not 1.
alter session set week_of_year_policy = 1;
vs
alter session set week_of_year_policy = 0;
WORKING CODE
create or replace temporary table test_temptable
(
DATE_ID SMALLINT NOT NULL
,FULL_DATE DATE NOT NULL
,DATE Varchar(10) NOT NULL
,YEAR SMALLINT NOT NULL
,WEEK_OF_YEAR SMALLINT NOT NULL
,DAY_OF_YEAR SMALLINT NOT NULL
,QTR_NUMBER SMALLINT NOT NULL
,DAY_OF_QUARTER SMALLINT NOT NULL
,MONTH_OF_YEAR SMALLINT NOT NULL
,MONTH_NAME CHAR(3) NOT NULL --need to have full month name, if it comes to it maybe do if logic
,DAY_OF_MONTH SMALLINT NOT NULL
,DAY_OF_WEEK VARCHAR(9) NOT NULL
,DAY_NAME VARCHAR(12) NOT NULL
,DAY_IS_WEEKDAY boolean NOT Null
,DAY_IS_LAST_OF_MONTH boolean NOT Null
,DAY_OF_WEEK_IN_MONTH SMALLINT NOT NULL
,HOLIDAYUSA VARCHAR(80) ----left out NOT NULL on Purpose
--- ,DAY_IS_HOLIDAY boolean NOT NULL
)
AS
WITH MY_DATES AS (
SELECT DATEADD(DAY, SEQ4(), '2005-01-01') AS Full_DATE
,(seq8()+ 1) AS date_id
,DATE_TRUNC('QUARTER',Full_DATE) as Q
,DATEDIFF('day',Q, Full_DATE) as Day_of_Quarter
/*logic to support Easter Day calculation */
,Full_DATE as SinCurDay
,MONTH(Full_Date) as inCurMonth
,YEAR(Full_Date) as inCurYear
,FLOOR(inCurYear/100) as inCurCent
,inCurYear%19 as inYear
,FLOOR((inCurCent-17)/25) as inYearTmp
,(inCurCent-FLOOR(inCurCent/4)-FLOOR((inCurCent-inYearTmp)/3)+(19*inYear)+15)%30 as inTemp2a
,inTemp2a-FLOOR(inTemp2a/28)*(1 - FLOOR(inTemp2a/28)*FLOOR(29/(inTemp2a+1))*FLOOR((21-inYear)/11)) as inTemp2b
,(inCurYear+FLOOR(inCurYear/4)+inTemp2b+2-inCurCent+FLOOR(inCurCent/4))%7 as inTemp3
,inTemp2b-inTemp3 as inTemp4
,3+FLOOR((inTemp4+40)/44) as inEastMontha
,inTemp4+28-31*FLOOR(inEastMontha/4) as inEastDay
,inEastMontha /*- 1*/ as inEastMonthb
,Date_from_parts(inCurYear,inEastMonthb, inEastDay) as EasterDay
/*End Easter Day Logic */
/*Day of Week in Month*/
/* CASE
WHEN Day(Full_Date) < 8 THEN 1
WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4
ELSE 5 End as Day_In_Month
*/
FROM TABLE(GENERATOR(ROWCOUNT=>365))
)
SELECT date_id
,Full_Date
,to_varchar(Full_Date, 'mm/dd/yyyy')
,YEAR(Full_Date)
,WEEKOFYEAR(Full_Date)
,DAYOFYEAR(Full_Date)
,QUARTER(Full_Date)
,Day_Of_Quarter + 1
,MONTH(Full_Date)
,MONTHNAME(Full_Date)
,DAY(Full_Date)
,DAYOFWEEK(Full_Date) + 1
,DAYNAME(Full_Date)
/*Weekend boolean */
,CASE
WHEN DAYOFWEEK(Full_date) + 1 = 7 THEN FALSE
WHEN DAYOFWEEK(Full_date) + 1 = 1 THEN FALSE
ELSE TRUE END
/*Last Day Of Month Boolean*/
,CASE
WHEN Full_Date = last_day(Full_Date) THEN True
ELSE FALSE END
/*Week in Month*/
---,CAST(Round((day(Full_Date) +6)/7,0) as VARCHAR)
,CASE
WHEN Day(Full_Date) < 8 THEN 1
WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4
ELSE 5 End
/*HolidayUSA Logic */
,CASE
WHEN MONTH(Full_Date) = 10 AND DAY(Full_Date) = 31 THEN 'Halloween'
/*ThanksGiving*/
WHEN MONTH(Full_Date) = 11 AND DAYOFWEEK(Full_Date) + 1 = 5 AND
CASE
WHEN Day(Full_Date) < 8 THEN 1
WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4
ELSE 5 End = 4
THEN 'Thanksgiving Day' -- should I add ()
WHEN MONTH(Full_Date) = 11 AND DAYOFWEEK(Full_Date) + 1 = 6 AND
CASE
WHEN Day(Full_Date) < 8 THEN 1
WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4
ELSE 5 End = 4
THEN 'Black Friday' -- should I add ()
WHEN MONTH(Full_Date) = 12 AND DAY(Full_Date) = 25 THEN 'Christmas Day'
WHEN MONTH(Full_Date) = 7 AND DAY(Full_Date) = 4 THEN 'Independence Day'
WHEN MONTH(Full_Date) = 12 AND DAY(Full_Date) = 31 THEN 'New Years Eve'
WHEN MONTH(Full_Date) = 1 AND DAY(Full_Date) = 1 THEN 'New Years Day'
WHEN MONTH(Full_Date) = 5 AND DAYOFWEEK(Full_Date)+ 1 = 2 AND Day(Full_Date) > '24' then 'Memorial Day'
WHEN MONTH(Full_Date) = 9 AND DAYOFWEEK(Full_Date) + 1 = 2 AND Day(Full_Date) < '8'THEN 'Labor Day'
/*Martin Luther King Jr Day */
WHEN MONTH(Full_Date) = 1 AND DAYOFWEEK(Full_Date) + 1 = 2 AND
CASE
WHEN Day(Full_Date) < 8 THEN 1
WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4
ELSE 5 End
= 3 THEN 'Martin Luther King Jr Day'
/*Presidents Day*/
WHEN MONTH(Full_Date) = 2 AND DAYOFWEEK(Full_Date) + 1 = 2 AND
CASE
WHEN Day(Full_Date) < 8 THEN 1
WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4
ELSE 5 END
= 3 THEN 'Presidents Day'
WHEN MONTH(Full_Date) = 11 AND DAY(Full_Date) = 11 THEN 'Veterans Day'
/*Mothers Day */
WHEN MONTH(Full_Date) = 5 AND DAYOFWEEK(Full_Date) + 1 = 1 AND
CASE
WHEN Day(Full_Date) < 8 THEN 1
WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4
ELSE 5 END
= 2 THEN 'Mothers Day'
/*Fathers Day */
WHEN MONTH(Full_Date) = 6 AND DAYOFWEEK(Full_Date) + 1 = 1 AND
CASE
WHEN Day(Full_Date) < 8 THEN 1
WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4
ELSE 5 END
= 3 THEN 'Fathers Day'
WHEN MONTH(Full_Date) = 2 AND DAY(Full_Date) = 14 THEN 'Valentines Day'
WHEN Full_Date = EasterDay THEN 'Easter Day'
WHEN Full_Date = EasterDay - 2 THEN 'Good Friday'
ELSE NULL END
--- ,CASE
--- WHEN HOLIDAYUSA is not NULL THEN TRUE Else False
--- END
FROM MY_DATES
Order By Full_Date;
Evolved Question: Please view updated code, where I translated the T-SQL logic to Calculate Easter into something Snowflake could understand. The only issue is that for Year 2005 I am off by one day ( the code below returns March 28, 2005 for Easter but it is March 27, 2008).
Question: Can someone help me understand why the below line is so close but wrong, I have tried so many variations of rtrim, RIGHT, and other manipulations to the values that feed the date thinking it had to do with rounding down, but all making the return easter dates further off. Below is my closest for a 20 year span , all within about 1-5 days of actual.
Line that is causing error:date_from_parts(YR, '0' + rtrim(EasterMonth),'0' + rtrim(EasterDay)) AS test6
Easter Logic
(24 + 19 * (YR % 19)) % 30 AS EpactCalc,
EpactCalc - (EpactCalc / 28) AS PaschalDaysCalc,
PaschalDaysCalc - ((YR + YR / 4 + PaschalDaysCalc - 13) % 7) AS NumOfDaysToSunday,
3 + (NumOfDaysToSunday + 40) / 44 AS EasterMonth,
NumOfDaysToSunday + 28 - (31 * (EasterMonth / 4)) AS EasterDay, ---EasterMonth + RTRIM(YR) as test6
---to_date_from_parts(YR,(("0" + EasterMonth).substr(-2)), (("0" + EasterDay).substr(-2)) as test6
date_from_parts(YR, '0' + rtrim(EasterMonth),'0' + rtrim(EasterDay)) AS test6
Full Script:
CREATE OR REPLACE
TEMPORARY TABLE .test_temptable (Date_Id SMALLINT NOT NULL ,Full_Date DATE NOT NULL ,Date Varchar(10) NOT NULL ,YEAR SMALLINT NOT NULL ,WEEK_OF_YEAR SMALLINT NOT NULL ,DAY_OF_YEAR SMALLINT NOT NULL ,QTR_Number SMALLINT NOT NULL ,Day_Of_Quarter SMALLINT NOT NULL,MONTH_OF_YEAR SMALLINT NOT NULL ,MONTH_NAME CHAR(3) NOT NULL --need to have full month name, if it comes to it maybe do if logic
,DAY_OF_MONTH SMALLINT NOT NULL ,DAY_OF_WEEK VARCHAR(9) NOT NULL ,DAY_NAME VARCHAR(12) NOT NULL ,DAY_IS_WEEKDAY boolean NOT NULL,DAY_IS_LAST_OF_MONTH boolean NOT NULL ,DAY_OF_WEEK_IN_MONTH SMALLINT NOT NULL ,HOLIDAYUSA VARCHAR(80) ----left out NOT NULL on Purpose
,test1 smallint NOT NULL,test2 smallint NOT NULL ,test3 smallint NOT NULL ,test4 smallint NOT NULL,test5 smallint NOT NULL ,test6 DATE NOT NULL) AS WITH CTE_MY_DATE AS
(---Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (1, 2, 4, or 8 byte)..??I'd like to understand this a tad bit better.Is SEQ4 for float?
SELECT DATEADD(DAY, SEQ4(), '2005-01-01') AS Full_DATE,
YEAR(Full_Date) AS YR,
(seq8()+ 1) AS date_id,
DATE_TRUNC('QUARTER',Full_DATE) AS q,
DATEDIFF('day',q, Full_DATE) AS Day_of_Quarter,
(24 + 19 * (YR % 19)) % 30 AS EpactCalc,
EpactCalc - (EpactCalc / 28) AS PaschalDaysCalc,
PaschalDaysCalc - ((YR + YR / 4 + PaschalDaysCalc - 13) % 7) AS NumOfDaysToSunday,
3 + (NumOfDaysToSunday + 40) / 44 AS EasterMonth,
NumOfDaysToSunday + 28 - (31 * (EasterMonth / 4)) AS EasterDay, ---EasterMonth + RTRIM(YR) as test6
---to_date_from_parts(YR,(("0" + EasterMonth).substr(-2)), (("0" + EasterDay).substr(-2)) as test6
date_from_parts(YR, '0' + rtrim(EasterMonth),'0' + rtrim(EasterDay)) AS test6
FROM TABLE(GENERATOR(ROWCOUNT=>9125))
)
SELECT date_id ,
Full_Date ,
to_varchar(Full_Date, 'mm/dd/yyyy') ,
YEAR(Full_Date) ,
WEEKOFYEAR(Full_Date) ,
DAYOFYEAR(Full_Date) ,
QUARTER(Full_Date) ,
Day_Of_Quarter + 1 ,
MONTH(Full_Date) ,
MONTHNAME(Full_Date) ,
DAY(Full_Date) ,
DAYOFWEEK(Full_Date) + 1 ,
DAYNAME(Full_Date) ---calculates if it is on weekend or not
,
CASE
WHEN DAYOFWEEK(Full_date) = 7 THEN FALSE
WHEN DAYOFWEEK(Full_date) = 1 THEN FALSE
ELSE TRUE
END ----calculates if last day of month
,
CASE
WHEN Full_Date = last_day(Full_Date) THEN TRUE
ELSE FALSE
END,
CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) -- this is what week you are in in the month, double check that what it ought to be
--- calculates holidays, is Thxgiving always in the fifth week?,
,
CASE
WHEN MONTH(Full_Date) = 10
AND DAY(Full_Date) = 31 THEN 'Halloween'
WHEN MONTH(Full_Date) = 11
AND DAYOFWEEK(Full_Date) + 1 = 4
AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 5 THEN 'Thanksgiving Day'
WHEN MONTH(Full_Date) = 12
AND DAY(Full_Date) = 25 THEN 'Christmas Day'
WHEN MONTH(Full_Date) = 7
AND DAY(Full_Date) = 4 THEN 'Independence Day' --adding
WHEN MONTH(Full_Date) = 12
AND DAY(Full_Date) = 31 THEN 'New Years Eve'
WHEN MONTH(Full_Date) = 1
AND DAY(Full_Date) = 1 THEN 'New Years Day' ---memorial day attempt
WHEN MONTH(Full_Date) = 5
AND DAYOFWEEK(Full_Date)+ 1 = 2
AND Day(Full_Date) > '24' THEN 'Memorial Day' ---labor day
WHEN MONTH(Full_Date) = 9
AND DAYOFWEEK(Full_Date) + 1 = 2
AND Day(Full_Date) < '8'THEN 'Labor Day'
WHEN MONTH(Full_Date) = 1
AND DAYOFWEEK(Full_Date) = 2
AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Martin Luther King Jr Day'
WHEN MONTH(Full_Date) = 2
AND DAYOFWEEK(Full_Date) = 2
AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Presidents Day'
WHEN MONTH(Full_Date) = 11
AND DAY(Full_Date) = 11 THEN 'Veterans Day' ---added Mother's Day
WHEN MONTH(Full_Date) = 5
AND DAYOFWEEK(Full_Date) + 1 = 1
AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 2 THEN 'Mothers Day'
WHEN MONTH(Full_Date) = 6
AND DAYOFWEEK(Full_Date) + 1 = 1
AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Fathers Day'
WHEN MONTH(Full_Date) = 2
AND DAY(Full_Date) = 14 THEN 'Valentines Day' ---easter
---good friday
ELSE NULL
END ,
EpactCalc ,
PaschalDaysCalc ,
NumOfDaysToSunday ,
EasterMonth ,
EasterDay ,
test6
FROM CTE_MY_DATE;
Old Question: I have this nice piece of code for snowflake users that I need a little help finishing. I specifically want to use the second chunk of code that was written for SqlServer to be used in Snowflakes env, and integrated into my script below (first chunk of code).
Specifically:
a)"How do you integrate a function into a query like this" as in Advice where to put the code inside my script above because I am having trouble understanding how to integrate a function within a select statement
b)"Is there anything glaring about this query that would make running it in Snowflake uniquely difficult" I attempted to run the SQLServer "easter date" code alone inside snowflake, and changed the variables to match snowflakes requirements (ie take out @) and then I got an error unexpected 'BEGIN'.
CREATE OR REPLACE
TEMPORARY TABLE test_temptable (Date_Id SMALLINT NOT NULL ,Full_Date DATE NOT NULL ,Date Varchar(10) NOT NULL ,YEAR SMALLINT NOT NULL ,WEEK_OF_YEAR SMALLINT NOT NULL ,DAY_OF_YEAR SMALLINT NOT NULL ,QTR_Number SMALLINT NOT NULL ,Day_Of_Quarter SMALLINT NOT NULL,MONTH_OF_YEAR SMALLINT NOT NULL ,MONTH_NAME CHAR(3) NOT NULL
,DAY_OF_MONTH SMALLINT NOT NULL ,DAY_OF_WEEK VARCHAR(9) NOT NULL ,DAY_NAME VARCHAR(12) NOT NULL ,DAY_IS_WEEKDAY boolean NOT NULL,DAY_IS_LAST_OF_MONTH boolean NOT NULL ,DAY_OF_WEEK_IN_MONTH SMALLINT NOT NULL ,HOLIDAYUSA VARCHAR(80)
(
SELECT DATEADD(DAY, SEQ4(), '2005-01-01') AS Full_DATE,
(seq8()+ 1) AS date_id,
DATE_TRUNC('QUARTER',Full_DATE) AS q,
DATEDIFF('day',q, Full_DATE) AS Day_of_Quarter
FROM TABLE(GENERATOR(ROWCOUNT=>366))
)
SELECT date_id ,
Full_Date ,
to_varchar(Full_Date, 'mm/dd/yyyy') ,
YEAR(Full_Date) ,
WEEKOFYEAR(Full_Date) ,
DAYOFYEAR(Full_Date) ,
QUARTER(Full_Date) ,
Day_Of_Quarter + 1 ,
MONTH(Full_Date) ,
MONTHNAME(Full_Date) ,
DAY(Full_Date) ,
DAYOFWEEK(Full_Date) + 1 ,
DAYNAME(Full_Date)
,
CASE
WHEN DAYOFWEEK(Full_date) = 7 THEN FALSE
WHEN DAYOFWEEK(Full_date) = 1 THEN FALSE
ELSE TRUE
END
,
CASE
WHEN Full_Date = last_day(Full_Date) THEN TRUE
ELSE FALSE
END,
CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR)
,
CASE
WHEN MONTH(Full_Date) = 10
AND DAY(Full_Date) = 31 THEN 'Halloween'
WHEN MONTH(Full_Date) = 11
AND DAYOFWEEK(Full_Date) + 1 = 4
AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 5 THEN 'Thanksgiving Day'
WHEN MONTH(Full_Date) = 12
AND DAY(Full_Date) = 25 THEN 'Christmas Day'
WHEN MONTH(Full_Date) = 7
AND DAY(Full_Date) = 4 THEN 'Independence Day' --adding
WHEN MONTH(Full_Date) = 12
AND DAY(Full_Date) = 31 THEN 'New Years Eve'
WHEN MONTH(Full_Date) = 1
AND DAY(Full_Date) = 1 THEN 'New Years Day' ---memorial day attempt
WHEN MONTH(Full_Date) = 5
AND DAYOFWEEK(Full_Date)+ 1 = 2
AND Day(Full_Date) > '24' THEN 'Memorial Day' ---labor day
WHEN MONTH(Full_Date) = 9
AND DAYOFWEEK(Full_Date) + 1 = 2
AND Day(Full_Date) < '8'THEN 'Labor Day'
WHEN MONTH(Full_Date) = 1
AND DAYOFWEEK(Full_Date) = 2
AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Martin Luther King Jr Day'
WHEN MONTH(Full_Date) = 2
AND DAYOFWEEK(Full_Date) = 2
AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Presidents Day'
WHEN MONTH(Full_Date) = 11
AND DAY(Full_Date) = 11 THEN 'Veterans Day' ---added Mother's Day
WHEN MONTH(Full_Date) = 5
AND DAYOFWEEK(Full_Date) + 1 = 1
AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 2 THEN 'Mothers Day'
WHEN MONTH(Full_Date) = 6
AND DAYOFWEEK(Full_Date) + 1 = 1
AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Fathers Day'
WHEN MONTH(Full_Date) = 2
AND DAY(Full_Date) = 14 THEN 'Valentines Day' ---easter
---good friday
ELSE NULL
END
FROM CTE_MY_DATE;
Below is the SQLServer code I need help to input the above!! (thanks Function to return date of Easter for the given year)
CREATE FUNCTION dbo.GetEasterSunday
( @Y INT )
RETURNS SMALLDATETIME
AS
BEGIN
DECLARE @EpactCalc INT,
@PaschalDaysCalc INT,
@NumOfDaysToSunday INT,
@EasterMonth INT,
@EasterDay INT
SET @EpactCalc = (24 + 19 * (@Y % 19)) % 30
SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28)
SET @NumOfDaysToSunday = @PaschalDaysCalc - (
(@Y + @Y / 4 + @PaschalDaysCalc - 13) % 7
)
SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44
SET @EasterDay = @NumOfDaysToSunday + 28 - (
31 * (@EasterMonth / 4)
)
RETURN
(
SELECT CONVERT
( SMALLDATETIME,
RTRIM(@Y)
+ RIGHT('0'+RTRIM(@EasterMonth), 2)
+ RIGHT('0'+RTRIM(@EasterDay), 2)
)
)
END
GO
New answer: You must use month and day numerical values directly, not reformat as TEXT:
DATE_FROM_PARTS(Year, EasterMonth, EasterDay)
Old answer:
It should be fairly simple to convert your T-SQL function to a Snowflake JavaScript function.
Maybe you have to learn JavaScript on the way, though.
The skeleton of such a function can be:
CREATE OR REPLACE FUNCTION GetEasterSunday(Y FLOAT) RETURNS STRING LANGUAGE JAVASCRIPT AS
$$
var EpactCalc = (24 + 19 * (Y % 19)) % 30;
// more stuff here
var EasterMonth = 4, EasterDay = 21;
return Y + "-" + ("0" + EasterMonth).substr(-2) + "-" + ("0" + EasterDay).substr(-2);
$$;
SELECT GetEasterSunday(2019)::DATE;