September 25, 2020 at 2:48 pm
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
September 25, 2020 at 11:53 pm
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?
September 26, 2020 at 2:13 am
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
September 26, 2020 at 2:56 am
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
October 1, 2020 at 10:09 am
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