john john - 2 months ago 7
SQL Question

SQL Query to find the past 3rd business day excluding holidays and weekends

I am trying to write the sql query to find the past 3rd business day excluding the holidays and weekends. Below is my approachs but it's not giving the correct solution. Please suggest

First approach:

alter function getdate(@givendate datetime)
returns datetime
AS
BEGIN
declare @calcdate datetime
declare @reqdate datetime
declare @count bit
set @count=1
select @calcdate=@givendate-4;
select @reqdate=@calcdate;
while(@calcdate<@givendate)
begin
while(@count=1)
begin
if exists(select 1 from dbo.holidays with(Nolock) where dbo.holidays.date=@reqdate)
begin
select @reqdate=@reqdate-1
end
else
begin
set @count=0
end
end

select @calcdate=@calcdate+1
end
return @reqdate
END


Second approach:

alter function dbo.getpast3day (@date date)
returns datetime
As
begin
declare @reqdate datetime;
declare @Newyears_day datetime;
declare @Martin_Luther_Kin_Birthday datetime;
declare @presidents_day datetime;
declare @Memorial_day datetime;
declare @Independence_day datetime;
declare @Labour_day datetime;
declare @Columbus_day datetime;
declare @Veterans_day datetime;
declare @Thanksgiving_day datetime;
declare @Christmas_day datetime;
SELECT @reqdate= DATEADD(DAY, CASE (DATEPART(WEEKDAY, @date) + @@DATEFIRST) % 7
WHEN 1 THEN -5
WHEN 2 THEN -6
WHEN 3 THEN -6
WHEN 4 THEN -6
WHEN 5 THEN -6
ELSE -4
END, DATEDIFF(DAY, 0, @date)) ;
--@Martin_Luther_Kin_Birthday
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @Martin_Luther_Kin_Birthday=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=15 and DATEPART(day, thedate)<=21 and DATEPART(month,thedate)=1 and datepart(year,thedate)= year(@date);
end
--@presidents_day
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @presidents_day=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=15 and DATEPART(day, thedate)<=21 and DATEPART(month,thedate)=2 and datepart(year,thedate)= year(@date);
end
--@Memorial_day
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @Memorial_day=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=22 and DATEPART(day, thedate)<=31 and DATEPART(month,thedate)=2 and datepart(year,thedate)= year(@date);
end
--@Labour_day
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @Labour_day=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=1 and DATEPART(day, thedate)<=7 and DATEPART(month,thedate)=9 and datepart(year,thedate)= year(@date);
end
--@Columbus_day
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @Columbus_day=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=8 and DATEPART(day, thedate)<=14 and DATEPART(month,thedate)=10 and datepart(year,thedate)= year(@date);
end
--@Thanksgiving_day
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @Thanksgiving_day=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=22 and DATEPART(day, thedate)<=31 and DATEPART(month,thedate)=11 and datepart(year,thedate)= year(@date);
end

if (@reqdate=@Martin_Luther_Kin_Birthday or @reqdate=@presidents_day or @reqdate=@Memorial_day or @reqdate=@Labour_day or @reqdate=@Columbus_day or @reqdate=@Thanksgiving_day)
begin
select @reqdate= DATEADD(day,-6,@reqdate)
end

--RETURN '2216-09-20 00:00:00.000'


return @reqdate

end

Answer

Not 100% clear on what past 3rd business day is, but perhaps this can help. Clearly you will have to decide what a holiday is.

Select [dbo].[SomeFunctionName]('2016-01-20',3)

Returns

2016-01-15

The custom function is:

CREATE FUNCTION [dbo].[SomeFunctionName](@Date date,@nDay int)
Returns Date
As
Begin
 Declare @RetVal Date

;with cteHolidays as (
    Select * from (Values 
        ('2016-01-01','New Year''s Day'),
        ('2016-01-18','Martin Luther King, Jr,'),
        ('2016-02-15','Washington''s Birthday'),
        ('2016-03-25','Good Friday'),
        ('2016-05-30','Memorial Day'),
        ('2016-07-04','Independence Day'),
        ('2016-09-05','Labor Day'),
        ('2016-11-24','Thanksgiving'),
        ('2016-11-25','Black Friday'),
        ('2016-12-26','Christmas Day')
        ) as Holidays (Date,Name)
), cteDays as (
Select *
      ,RowNr = Row_Number() over (Order By RetVal Desc)
 From  [dbo].[udf-Create-Range-Date](DateAdd(DD,-3*@nDay,@Date),@Date,'DD',1)
 Where RetVal not in (Select Date from cteHolidays)
   and DatePart(DW,RetVal) between 2 and 6
)
Select @RetVal=RetVal
 From cteDays 
 Where RowNr=@nDay

Return @RetVal

End

It does require a generic helper function which I use to create dynamic date ranges

The helper function

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )

    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)

    Return
End

-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1) 

EDIT

To validate the example listed above

Date        Weekday      BusDayNr
2016-01-20  Wednesday    1        << Selected Date
2016-01-19  Tuesday      2
2016-01-18  Monday       -        << Holiday Martin Luther King, Jr
2016-01-17  Sunday       -
2016-01-16  Saturday     -
2016-01-15  Friday       3        << Date Returned
2016-01-14  Thursday
2016-01-13  Wednesday
2016-01-12  Tuesday
2016-01-11  Monday