sql - sql - 复活节/耶稣受难节的星期五

112 0

引起错误的行: date_from_parts(YR, '0' + rtrim(EasterMonth),'0' + rtrim(EasterDay)) AS test6

复活节逻辑


(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



完整脚本:


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;




 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;



下面是SQLServer代码,我需要帮助输入上面的代码!


 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



时间: 原作者:

75 0


此类函数的骨架可以是:


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; 



原作者:
...