Need help wiith holiday logic when it falls on a weekend

blackhills 136 Reputation points
2020-10-21T15:47:42.4+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
Accepted answer
  1. blackhills 136 Reputation points
    2020-10-22T19:57:42.423+00:00

    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
);

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. 21986829 481 Reputation points
    2020-10-21T18:00:50.983+00:00

    ;With cteHolidays AS
    ( SELECT *,
    CASE
    WHEN c.[DAYNAME] = 'SUNDAY' THEN DATEADD( d, 1, CALENDAR_DATE)
    WHEN c.[DAYNAME] = 'SATURDAY' THEN DATEADD( d, -1, CALENDAR_DATE)
    ELSE CALENDAR_DATE END AS NewHoliday,
    CASE
    WHEN c.[DAYNAME] = 'SUNDAY' THEN 'C'
    WHEN c.[DAYNAME] = 'SATURDAY' THEN 'C'
    ELSE c.WORKDAYFLAG END AS NewHolidayFlag
    FROM WORKDAYFLAG c WHERE WORKDAYFLAG='N')
    SELECT
    CASE WHEN b.NewHolidayFlag = 'C' THEN b.NewHoliday ELSE a.CALENDAR_DATE END AS CALENDAR_DATE
    , a.[DAYNAME],
    CASE WHEN b.NewHolidayFlag = 'C' THEN 'Y' ELSE a.WORKDAYFLAG END AS WORKDAYFLAG
    FROM WORKDAYFLAG a
    LEFT JOIN cteHolidays b on b.CALENDAR_DATE = a.CALENDAR_DATE

  2. Ian Bates 91 Reputation points
    2020-10-21T21:29:32.423+00:00

    Hi, maybe something like this.

    WITH Dates AS (
    SELECT '12/22/2016' AS Date
    UNION ALL SELECT '12/23/2016'
    UNION ALL SELECT '12/24/2016'
    UNION ALL SELECT '12/25/2016'
    UNION ALL SELECT '12/26/2016'
    UNION ALL SELECT '12/27/2016'
)

SELECT D.Date AS CALENDAR_DATE
    ,DATENAME(DW,D.Date) AS DAYNAME
    ,CASE WHEN MONTH(D.Date)=12 AND DATEPART(D,D.Date) IN (23,26) -- Your specific dates
            THEN CASE WHEN DATEPART(DW,D.Date) NOT IN (1,7) AND DATEPART(DW,'12/25/2016')<>1 THEN 'Y' ELSE 'N' END
        WHEN DATEPART(DW,D.Date) NOT IN (1,7) THEN 'Y' -- Sets all weekdays to Yes
        WHEN DATEPART(DW,D.Date) IN (1,7) THEN 'N' -- Sets all weekends to No
        END AS WORKDAYFLAG
FROM Dates D
    0 comments
  3. Erland Sommarskog 101.8K Reputation points MVP
    2020-10-21T21:38:21.89+00:00

    I guess you fill this table once? In that case: 

    UPDATE WORKDAYFLAG
SET      WorkDayFlag = 'N'
WHERE substring(convert(8), CALENDAR_DATE, 112), 5, 4) = '1223' 
  AND  datename(WEEKDAY, CALENDAR_DATE) = 'Friday'

    And similar for Boxing Day.

    I prefer to use datename over datepart for weekdays to avoid the issue when the week starts.

  4. MelissaMa-MSFT 24,176 Reputation points
    2020-10-22T01:36:22.79+00:00

    Hi @blackhills ,

    Thank you so much for posting here.

    Please refer below and check whether it is working. 

    UPDATE WORKDAYFLAG   
SET WORKDAYFLAG='N'  
WHERE YEAR(CALENDAR_DATE) IN   
    (SELECT YEAR(CALENDAR_DATE)   
     FROM WORKDAYFLAG   
     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','1226');

    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.

    0 comments