How to check if NextDay is Holiday (exclude Saturday and Sunday)?

  • Hi:

    We maintain a calendar table with these columns. We have a process which runs daily and one of step in that is to take different action if the next day (Monday/Tuesday/Wednesday/Thursday/Friday) is holiday. If it is not a holiday then no action is required.

    create table #Calendar
    (CalendarDate date,
    DayOfWeek int,
    BusinessDayFlag bit,
    NextBusinessDay date,
    HolidayName varchar(100))

    INSERT INTO #Calendar values ('2020-09-03',5,1,'2020-09-04','')
    INSERT INTO #Calendar values ('2020-09-04',6,1,'2020-09-08','')
    INSERT INTO #Calendar values ('2020-09-05',7,0,'2020-09-08','')
    INSERT INTO #Calendar values ('2020-09-06',1,0,'2020-09-08','')
    INSERT INTO #Calendar values ('2020-09-07',2,0,'2020-09-08','Labor Day')
    INSERT INTO #Calendar values ('2020-09-08',3,1,'2020-09-09','')
    INSERT INTO #Calendar values ('2020-09-09',4,1,'2020-09-10','')
    INSERT INTO #Calendar values ('2020-09-10',5,1,'2020-09-11','')
    INSERT INTO #Calendar values ('2020-09-11',6,1,'2020-09-14','')
    INSERT INTO #Calendar values ('2020-09-12',7,0,'2020-09-14','')
    INSERT INTO #Calendar values ('2020-09-13',1,0,'2020-09-14','')
    INSERT INTO #Calendar values ('2020-09-14',2,1,'2020-09-15','')
    INSERT INTO #Calendar values ('2020-09-15',3,1,'2020-09-16','')

    select * from #Calendar
    order by CalendarDate

    SQL Query Help to figure out whether NextDay is Holiday (exclude Saturday and Sunday)?

    IF NextDay <> Holiday

    BEGIN

    print 'Take Action'

    END

    ELSE

    BEGIN

    print 'No Action'

    END

  • that is a straight basic sql with a top 1, a where clause and a order by. - either assign to a variable and check the contents of the variable or do it straight on the if statement.

     

    what difficulties do you have with either of the above?

  • Here are two ways depending on whether GETDATE() returns the date you're interested in.  If it does you could use something like this

    /* use GETDATE() to determine 'today' and add 1 day */
    select count(*) is_holiday
    from #Calendar
    where CalendarDate=dateadd(d, 1, Getdate())
    and HolidayName is not null;

    If the date needs to be specific to a time zone you could do something like this

    /* use getutcdate at time zone 'EST' to determine 'today' and add 1 day */
    select count(*) is_holiday
    from #Calendar
    where CalendarDate=dateadd(d, 1, getutcdate() at time zone 'Eastern Standard Time')
    and HolidayName is not null;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Just a note about the table design.  For this purpose all of the rows which are not a holiday are irrelevant.  Meaning if there were a table of just holidays we would query that instead.  Whenever you're storing a whole bunch of NULL values in a column it's time to consider if the logical model is properly normalized.  NULL-able foreign keys are permitted.  You're currently storing the string HolidayName in the Calendar table.  It could be normalized into it's own table called Holidays with a unique constraint on HolidayName and Date.  and the ID column of the Holidays table could be referenced as a foreign key by the Calendar table.  Then this query would run against Holidays instead of the whole Calendar.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • I'm sure that the table table is in fifth normalform. What do you think it's on (Steve Collins)?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply