June 27, 2019 at 11:37 pm
Hello all,
I have a stored procedure that runs an insert query.
Insert Into MyTable ( Etc..etc.)
Select Etc..etc
From Into MyTable
My server has a process to run this SP everyday along with a batch of others. However, in this particular SP, I need it only to run on a weekday only, omitting weekends or Public holidays (as defined in a separate table).
Can i define a criteria for this to be the case, and if so, how would I write it to check the day omitting a weekend or a public holiday?
Cheers
June 28, 2019 at 3:17 am
In the absence of better code, this is what I came up with. I'm assuming you have a table of holidays like #Holidays table. Then you just check for the day of the week if it's not a holiday. If it's not a holiday and not a weekend (days 1,7), then you execute your code, otherwise, skip over it.
CREATE TABLE #Holidays(HolidayDate DATE PRIMARY KEY);
GO
INSERT INTO #Holidays(HolidayDate) VALUES ('4-Jul-2019');
DECLARE @TheDate DATE = '04-Jul-2019';
--WHILE @TheDate <= '5-Jul-2019'
BEGIN
IF DATEPART(dw,@TheDate) = 1 OR DATEPART(dw,@TheDate) = 7
BEGIN
PRINT CAST(@TheDate AS VARCHAR(10)) + ' is a weekend';
END
IF EXISTS (SELECT 1 FROM #Holidays WHERE HolidayDate = @TheDate)
BEGIN
PRINT CAST(@TheDate AS VARCHAR(10)) + ' is a holiday.';
END
ELSE
BEGIN
PRINT 'Work day, so get busy!'
-- execute your workday code here.
END
END
June 28, 2019 at 5:37 am
In the absence of better code, this is what I came up with. I'm assuming you have a table of holidays like #Holidays table. Then you just check for the day of the week if it's not a holiday. If it's not a holiday and not a weekend (days 1,7), then you execute your code, otherwise, skip over it.
CREATE TABLE #Holidays(HolidayDate DATE PRIMARY KEY);
GO
INSERT INTO #Holidays(HolidayDate) VALUES ('4-Jul-2019');
DECLARE @TheDate DATE = '04-Jul-2019';
--WHILE @TheDate <= '5-Jul-2019'
BEGIN
IF DATEPART(dw,@TheDate) = 1 OR DATEPART(dw,@TheDate) = 7
BEGIN
PRINT CAST(@TheDate AS VARCHAR(10)) + ' is a weekend';
END
IF EXISTS (SELECT 1 FROM #Holidays WHERE HolidayDate = @TheDate)
BEGIN
PRINT CAST(@TheDate AS VARCHAR(10)) + ' is a holiday.';
END
ELSE
BEGIN
PRINT 'Work day, so get busy!'
-- execute your workday code here.
END
END
DATEPART will return different values depending in the DATEFIRST setting.
The following code will always result in Monday=0, ... Sunday=6
DECLARE @TheDate date = '04-Jul-2019';
IF DATEDIFF(dd, 0, @TheDate) %7 > 4
BEGIN
PRINT CAST(@TheDate AS varchar(10)) + ' is a weekend';
RETURN;
END;
June 28, 2019 at 8:16 am
This was removed by the editor as SPAM
June 28, 2019 at 3:03 pm
IF DATEDIFF(DAY, 0, GETDATE()) % 7 <= 4 /*0=Mon;4=Fri;5=Sat*/
AND NOT EXISTS(SELECT 1 FROM dbo.your_holiday_table yht WHERE yht.date = CAST(GETDATE() AS date))
BEGIN
INSERT INTO ...
END /*IF*/
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 28, 2019 at 7:27 pm
In addition, this sort of problem is a lot easier if you have a full-fledged calendar table, where you have an entry for each day, and where you have a flag for whether the day is a business day etc.
Here is a good article by Ed Pollack which describes this concept in more detail: https://www.sqlshack.com/designing-a-calendar-table/.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply