As per my comment, to get the exact date, 4 weeks earlier use
SELECT DATEADD(w, -4, @datevalue);
For the rest of the problem, you need to be able to identify holidays somehow. The way I like to do it is to have a table explicitly for dates and I like to store
all dates in a year.
This may seem silly when it's easy enough to generate a list of dates, but I prefer to do this as a regular maintenance task rather than each time I need the information.
My table looks a little like this
declare CalendarDates table (ddate date, dday nvarchar(30), isWorkingday bit, isHoliday bit, comments nvarchar(50));
Then to get the next working day I just need to use
select TOP 1 *
from CalendarDates
where ddate >= @calcdate and isWorkingday = 1
ORDER BY ddate asc
Note: The
TOP 1
and the
ORDER BY
are essential to make this work
Worked example:
insert into Calendardates(ddate, dday, isWorkingday, isHoliday, comments) values
('2021-Dec-14','Tuesday',1,0,'')
,('2021-Dec-15','Wednesday',1,0,'')
,('2021-Dec-16','Thursday',1,0,'')
,('2021-Dec-17','Friday',1,0,'')
,('2021-Dec-18','Saturday',0,0,'')
,('2021-Dec-19','Sunday',0,0,'')
,('2021-Dec-20','Monday',1,0,'')
,('2021-Dec-21','Tuesday',1,0,'')
,('2021-Dec-22','Wednesday',1,0,'')
,('2021-Dec-23','Thursday',1,0,'')
,('2021-Dec-24','Friday',1,0,'')
,('2021-Dec-25','Saturday',0,0,'')
,('2021-Dec-26','Sunday',0,0,'')
,('2021-Dec-27','Monday',0,1,'Christmas')
,('2021-Dec-28','Tuesday',0,1,'Boxing Day')
,('2021-Dec-29','Wednesday',1,0,'')
,('2021-Dec-30','Thursday',1,0,'')
,('2021-Dec-31','Friday',1,0,'')
,('2022-Jan-01','Saturday',0,0,'')
,('2022-Jan-02','Sunday',0,0,'')
,('2022-Jan-03','Monday',0,1,'New Year''s Day')
,('2022-Jan-04','Tuesday',1,0,'')
,('2022-Jan-05','Wednesday',1,0,'')
,('2022-Jan-06','Thursday',1,0,'');
declare @calcdate date = '2021-Dec-25';
returns
ddate dday isWorkingday isHoliday comments
2021-12-29 Wednesday 1 0