Need help wiith holiday logic when it falls on a weekend

Question

Hi All,
I am trying to come up with logic for holidays. Our company policy is 12/24 and 12/25 are both holidays , but when 12/25 falls on a sunday the preceding Friday and day after Monday are Holidays. For example in 2016 12/25 was a Sunday , i want to create a new column with a WORKDAYFLAG as Y and N. Please see the sample code, here the workdayflags are Y, but they need to be N. If christmas in on SATURDAY then the preceding FRIDAY AND SATURDAY should have value N for workdayflag, please advice.
CREATE TABLE WORKDAYFLAG (
CALENDAR_DATE DATE,
DAYNAME VARCHAR(10),
WORKDAYFLAG VARCHAR(1)
);
INSERT INTO WORKDAYFLAG (CALENDAR_DATE, DAYNAME,WORKDAYFLAG)
VALUES(‘12/25/2016’,SUNDAY,N)
VALUES(‘12/24/2016’,SATURDAY,N)
VALUES(‘12/23/2016’,FRIDAY,Y)
VALUES(‘12/26/2016’,MONDAY,Y)

So far i tried using LEAD and LAG functions and CASE statement , but not sure how to connect , like ( CASE WHEN HOLIDAYNAME='CHRISTMAS' AND DAYNAME='SUNDAY' THEN .........................

Thanks

Answer 1

Came up with something like this NOT sure if this is the correct way
but works for me

----------------    UPDATE WHEN CHISRSTMAS IS ON SUNDAY---------------------------

UPDATE CALENDAR_DATE_NEW
  SET CORP_HOLIDAY_IND='Y'
  WHERE CALENDAR_DATE IN  (
SELECT DISTINCT K.CALENDAR_DATE
FROM
(SELECT  CALENDAR_DATE ,DAY_IN_FISCAL_WEEK_DESC,CORP_HOLIDAY_IND
       FROM CALENDAR_DATE_NEW cd
      WHERE  YEAR(CALENDAR_DATE)>=2012
      AND DAY_IN_FISCAL_WEEK_DESC='SUNDAY'
      AND CORP_HOLIDAY_IND='Y'
      AND CONCAT(MONTH(CALENDAR_DATE),DAYOFMONTH(CALENDAR_DATE))=1225
   UNION ALL
   SELECT  DATEADD(DAY,1,CALENDAR_DATE) AS CALENDAR_DATE ,DAY_IN_FISCAL_WEEK_DESC,CORP_HOLIDAY_IND
       FROM CALENDAR_DATE_NEW cd
      WHERE  YEAR(CALENDAR_DATE)>=2012
      AND DAY_IN_FISCAL_WEEK_DESC='SUNDAY'
      AND CORP_HOLIDAY_IND='Y'
      AND CONCAT(MONTH(CALENDAR_DATE),DAYOFMONTH(CALENDAR_DATE))=1225
      UNION ALL
   SELECT  DATEADD(DAY,-1,CALENDAR_DATE) AS CALENDAR_DATE ,DAY_IN_FISCAL_WEEK_DESC,CORP_HOLIDAY_IND
       FROM CALENDAR_DATE_NEW cd
      WHERE  YEAR(CALENDAR_DATE)>=2012
      AND DAY_IN_FISCAL_WEEK_DESC='SUNDAY'
      AND CORP_HOLIDAY_IND='Y'
      AND CONCAT(MONTH(CALENDAR_DATE),DAYOFMONTH(CALENDAR_DATE))=1225
      UNION ALL
   SELECT  DATEADD(DAY,-2,CALENDAR_DATE) AS CALENDAR_DATE ,DAY_IN_FISCAL_WEEK_DESC,CORP_HOLIDAY_IND
       FROM CALENDAR_DATE_NEW cd
      WHERE  YEAR(CALENDAR_DATE)>=2012
      AND DAY_IN_FISCAL_WEEK_DESC='SUNDAY'
      AND CORP_HOLIDAY_IND='Y'
      AND CONCAT(MONTH(CALENDAR_DATE),DAYOFMONTH(CALENDAR_DATE))=1225
 )K
);
-----------------------------------------WHEN CHRISTMAS ON  SATURDAY---------------------------------------------

UPDATE CALENDAR_DATE_NEW
  SET CORP_HOLIDAY_IND='Y'
  WHERE CALENDAR_DATE IN  (
SELECT DISTINCT K.CALENDAR_DATE
FROM
(SELECT  CALENDAR_DATE ,DAY_IN_FISCAL_WEEK_DESC,CORP_HOLIDAY_IND
       FROM CALENDAR_DATE_NEW cd
      WHERE  YEAR(CALENDAR_DATE)>=2012
      AND DAY_IN_FISCAL_WEEK_DESC='SATURDAY'
      AND CORP_HOLIDAY_IND='Y'
      AND CONCAT(MONTH(CALENDAR_DATE),DAYOFMONTH(CALENDAR_DATE))=1225
   UNION ALL
   SELECT  DATEADD(DAY,1,CALENDAR_DATE) AS CALENDAR_DATE ,DAY_IN_FISCAL_WEEK_DESC,CORP_HOLIDAY_IND
       FROM CALENDAR_DATE_NEW cd
      WHERE  YEAR(CALENDAR_DATE)>=2012
      AND DAY_IN_FISCAL_WEEK_DESC='SATURDAY'
      AND CORP_HOLIDAY_IND='Y'
      AND CONCAT(MONTH(CALENDAR_DATE),DAYOFMONTH(CALENDAR_DATE))=1225
      UNION ALL
   SELECT  DATEADD(DAY,-1,CALENDAR_DATE) AS CALENDAR_DATE ,DAY_IN_FISCAL_WEEK_DESC,CORP_HOLIDAY_IND
       FROM CALENDAR_DATE_NEW cd
      WHERE  YEAR(CALENDAR_DATE)>=2012
      AND DAY_IN_FISCAL_WEEK_DESC='SATURDAY'
      AND CORP_HOLIDAY_IND='Y'
      AND CONCAT(MONTH(CALENDAR_DATE),DAYOFMONTH(CALENDAR_DATE))=1225
      UNION ALL
   SELECT  DATEADD(DAY,-2,CALENDAR_DATE) AS CALENDAR_DATE ,DAY_IN_FISCAL_WEEK_DESC,CORP_HOLIDAY_IND
       FROM CALENDAR_DATE_NEW cd
      WHERE  YEAR(CALENDAR_DATE)>=2012
      AND DAY_IN_FISCAL_WEEK_DESC='SATURDAY'
      AND CORP_HOLIDAY_IND='Y'
      AND CONCAT(MONTH(CALENDAR_DATE),DAYOFMONTH(CALENDAR_DATE))=1225
 )K
);

Answer 2

Hi @blackhills ,

Please find the updated query from below and check whether it is working.

DROP TABLE IF EXISTS CALENDAR_DATE  
  
CREATE TABLE CALENDAR_DATE (CALENDAR_DATE DATE,DAY_IN_FISCAL_WEEK_DESC VARCHAR(10) ,CORP_HOLIDAY_IND VARCHAR(1));  
  
INSERT INTO CALENDAR_DATE (CALENDAR_DATE,DAY_IN_FISCAL_WEEK_DESC, CORP_HOLIDAY_IND)  
VALUES  
('12/25/2016','SUNDAY','Y'),  
('12/24/2016','SATURDAY','N'),  
('12/23/2016','FRIDAY','N'),  
('12/26/2016','MONDAY','N'),  
('12/25/2010','SATURDAY','Y'),  
('12/24/2010','FRIDAY','N'),  
('12/27/2010','MONDAY','N'),  
('12/26/2010','SUNDAY','N'),  
('12/23/2010','THURSDAY','N')  
  
--CHRISTMAS LANDS ON SUNDAY  
 UPDATE CALENDAR_DATE   
 SET CORP_HOLIDAY_IND='Y'  
 WHERE YEAR(CALENDAR_DATE) IN   
     (SELECT YEAR(CALENDAR_DATE)   
      FROM CALENDAR_DATE   
      WHERE SUBSTRING(CONVERT(NVARCHAR(30),CALENDAR_DATE, 112),5,4) = '1225'   
      AND DATENAME(WEEKDAY,CALENDAR_DATE) = 'Sunday')  
 AND SUBSTRING(CONVERT(NVARCHAR(30),CALENDAR_DATE, 112), 5,4) IN ('1223','1224','1225','1226');  
  
 --CHRISTMAS LANDS ON SATURDAY  
  UPDATE CALENDAR_DATE   
 SET CORP_HOLIDAY_IND='Y'  
 WHERE YEAR(CALENDAR_DATE) IN   
     (SELECT YEAR(CALENDAR_DATE)   
      FROM CALENDAR_DATE   
      WHERE SUBSTRING(CONVERT(NVARCHAR(30),CALENDAR_DATE, 112),5,4) = '1225'   
      AND DATENAME(WEEKDAY,CALENDAR_DATE) = 'Saturday')  
 AND SUBSTRING(CONVERT(NVARCHAR(30),CALENDAR_DATE, 112), 5,4) IN ('1224','1225','1226');  
  
 SELECT * FROM CALENDAR_DATE  

Output:

CALENDAR_DATE	DAY_IN_FISCAL_WEEK_DESC	CORP_HOLIDAY_IND  
2016-12-25	SUNDAY	Y  
2016-12-24	SATURDAY	Y  
2016-12-23	FRIDAY	Y  
2016-12-26	MONDAY	Y  
2010-12-25	SATURDAY	Y  
2010-12-24	FRIDAY	Y  
2010-12-27	MONDAY	N  
2010-12-26	SUNDAY	Y  
2010-12-23	THURSDAY	N  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thanks Melissa ,
It is not working. I did the update and then after that i did a select statement and nothing changed. Do i need to do a commit after the update?
It only updated "2016-12-25" SUNDAY which already shows it as Y, I updated the SQL as i am using SNOWFLAKE (cloud based SQL Server) and i used (DAY_IN_FISCAL_WEEK_DESC) as it already exists in the CALENDAR_DATE

 --CHRISTMAS LANDS ON SUNDAY
 UPDATE CALENDAR_DATE_NEW
  SET CORP_HOLIDAY_IND='Y'
  WHERE CALENDAR_DATE IN

  (
       SELECT  CALENDAR_DATE 
       FROM CALENDAR_DATE_NEW cd
       where concat(month(cd.calendar_date),dayofmonth(cd.calendar_date))='1225'
           AND DAY_IN_FISCAL_WEEK_DESC='SUNDAY'
       and concat(month(cd.calendar_date),dayofmonth(cd.calendar_date)) IN ('1223','1224','1225','1226')
    AND YEAR(CALENDAR_DATE)>=2012);