calculate 3 working days before in stored proc using Holidays lookup table

Question

I have a tricky issue I am struggling with on a mental level.

In our db we have a table showing the UK Holidays for the next few years, and a stored function returns a recordset to my front end.

I have a flag in my recordset called 'deletable' which allows the frontend to decide if a context menu can be shown in the data grid, thus allowing that record to be deleted.

Currently the test (in my stored proc) just checks if the date column has a date from three days ago or more.

case when DATEDIFF(d,a.[date],GETDATE()) > 3 then 1 else 0 end as [deletable]

how can I modify that to find the previous working date by checking weekends and the Holidays table 'Holiday' column (which is a Datetime) and see if the [date] column in my recordset row is 3 working days before, taking into account Holidays from the Holidays table and weekends?

so if the [date] column is 23th May, and todays's date is 28th May, then that column returns 0, as the 27th was a bank holiday, whereas the next day it would return 1 because there would be more than 3 working days difference.

Is there an elegent way to do that?

thanks Philip

Answer 1

Okay I'm totally refactoring this.

declare
    @DeletablePeriodStart datetime,
    @BusinessDays int

set @DeletablePeriodStart = dateadd(d,0,datediff(d,0,getdate()))
set @BusinessDays = 0

while @BusinessDays < 3
begin
    set @DeletablePeriodStart = dateadd(d,-1,@DeletablePeriodStart)
    if datepart(dw,@DeletablePeriodStart) not in (1,7) and
        not exists (select * from HolidayTable where Holiday = @DeletablePeriodStart)
    begin
        set @BusinessDays = @BusinessDays + 1
    end
end

This time it doesn't make any assumptions. It runs a quick loop checking whether each day is a valid business day and doesn't stop till it counts three of them. Then later just check whether a.[date] >= @DeletablePeriodStart

Answer 2

You should substract the number of holidays between a.[date] and GETDATE() from the DATEDIFF. Try something like this:

case when DATEDIFF(d,a.[date],GETDATE())-(
    SELECT COUNT(*) FROM Holidays 
    WHERE HolidayDate BETWEEN a.[date] AND GETDATE()
)>3 then 1 else 0 end as [deletable]

Razvan

Answer 3

I am assuming that you don't have a Calendar table, although I'd highly recommend creating one, you can still achieve this without one:

The following will just get you a list of 2047 dates from yesterday going backwards (using the system table Master..spt_values):

WITH Dates AS
(   SELECT  Date = DATEADD(DAY, -number, CAST(GETDATE() AS DATE))
    FROM    Master..spt_values
    WHERE   type = 'P'
    AND     number > 0
)
SELECT  Dates.Date
FROM    Dates
ORDER BY Dates.Date DESC;

You then need to exclude weekends, and holidays from your table using this:

SET DATEFIRST 1;

WITH Dates AS
(   SELECT  Date = DATEADD(DAY, -number, CAST(GETDATE() AS DATE))
    FROM    Master..spt_values
    WHERE   type = 'P'
    AND     number > 0
)
SELECT  Dates.Date
FROM    Dates
WHERE   DATEPART(WEEKDAY, Dates.Date) <= 5
AND     NOT EXISTS
        (   SELECT  1
            FROM    HolidayTable h
            WHERE   Dates.Date = h.HolidayDate
        )
ORDER BY Dates.Date DESC;

N.B. You should explicitly set your DATEFIRST and not rely on server defaults

The above gives you a list of working days prior to today, you can then use the ROW_NUMBER() function, get the 3rd occurance in the list, giving a final query:

WITH Dates AS
(   SELECT  Date = DATEADD(DAY, -number, CAST(GETDATE() AS DATE))
    FROM    Master..spt_values
    WHERE   type = 'P'
    AND     number > 0
), WorkingDays AS
(   SELECT  Dates.Date, RN = ROW_NUMBER() OVER(ORDER BY Dates.Date DESC)
    FROM    Dates
    WHERE   DATEPART(WEEKDAY, Dates.Date) <= 5
    AND     NOT EXISTS
            (   SELECT  1
                FROM    HolidayTable h
                WHERE   Dates.Date = h.HolidayDate
            )
)
SELECT  WorkingDays.Date
FROM    WorkingDays
WHERE   RN = 3;

Or if you prefer this can be done with one query (exact same principle as above):

SELECT  d.Date
FROM    (   SELECT  Date = DATEADD(DAY, -number, CAST(GETDATE() AS DATE)), RN = ROW_NUMBER() OVER(ORDER BY number)
            FROM    Master..spt_values
            WHERE   type = 'P'
            AND     number > 0
            AND     DATEPART(WEEKDAY, DATEADD(DAY, -number, CAST(GETDATE() AS DATE))) <= 5
            AND     NOT EXISTS
                    (   SELECT  1
                        FROM    HolidayTable h
                        WHERE   DATEADD(DAY, -number, CAST(GETDATE() AS DATE)) = h.HolidayDate
                    )
        ) d
WHERE   rn = 3;