T-SQL - How to get difference in days w/o yearly holidays

Question

Pretty much, I'm trying to get the difference in days between two days w/o counting holidays. I've hard coded the holiday days, which works fine for me for now, but can seems to get my code to filter out those days. Any tips or advise would be greatly appreciated.

ALTER FUNCTION [dbo].[ufn_number_of_business_days]
    (
    @vstartdate as datetime,
    @venddate   as datetime
    )

 RETURNS int

 AS

 BEGIN

 declare @vnumberofdays as INT

    if @vstartdate >= @venddate
         set @vnumberofdays = 0

    ELSE

    IF 

    REPLACE(CONVERT(VARCHAR(8),CAST (@vstartdate AS DATE),110), '-', '') IN
                ('01012007', '05282007', '07042007', '09032007', '11222007',
                '11232007', '12242007', '12252007', '12312007', '05262008',
                '07032008', '07042008', '09012008', '11272008', '11282008',
                '12252008', '12262008', '01012009', '01022009', '05252009',
                '07032009', '09072009', '11262009', '11272009', '12252009',
                '01012010', '05312010', '07022010', '07052010', '09062010',
                '11252010', '11262010', '12242010', '12272010', '12282010',
                '12292010', '12302010', '12312010', '05302011', '07012011', 
                '07042011', '09052011', '11242011', '11252011', '12262011', 
                '12272011', '12282011', '12292011', '12302011', '01022012', 
                '05282012', '07042012', '07052012', '07062012', '09032012',
                '11222012', '11232012', '12242012', '12252012', '12262012', 
                '12272012', '12282012', '12312012', '01012013', '05272013',
                '07042013', '07052013', '09022013', '11282013', '11292013',
                '12242013', '12252013', '12262013', '12272013', '12302013', 
                '12312013', '01012014', '05262014', '07032014', '07042014',
                '09012014', '11272014', '11282014', '12242014', '12252014',
                '12262014', '12292014', '01012015', '05252015') 
    BEGIN

    set @vstartdate = 0

    END

    ELSE

    IF 

    REPLACE(CONVERT(VARCHAR(8),CAST (@venddate AS DATE),110), '-', '') IN
                ('01012007', '05282007', '07042007', '09032007', '11222007',
                '11232007', '12242007', '12252007', '12312007', '05262008',
                '07032008', '07042008', '09012008', '11272008', '11282008',
                '12252008', '12262008', '01012009', '01022009', '05252009',
                '07032009', '09072009', '11262009', '11272009', '12252009',
                '01012010', '05312010', '07022010', '07052010', '09062010',
                '11252010', '11262010', '12242010', '12272010', '12282010',
                '12292010', '12302010', '12312010', '05302011', '07012011', 
                '07042011', '09052011', '11242011', '11252011', '12262011', 
                '12272011', '12282011', '12292011', '12302011', '01022012', 
                '05282012', '07042012', '07052012', '07062012', '09032012',
                '11222012', '11232012', '12242012', '12252012', '12262012', 
                '12272012', '12282012', '12312012', '01012013', '05272013',
                '07042013', '07052013', '09022013', '11282013', '11292013',
                '12242013', '12252013', '12262013', '12272013', '12302013', 
                '12312013', '01012014', '05262014', '07032014', '07042014',
                '09012014', '11272014', '11282014', '12242014', '12252014',
                '12262014', '12292014', '01012015', '05252015')


    set @venddate = 0

    ELSE

    BEGIN

    SET @vnumberofdays = replace(convert(int, @vstartdate) - convert(int, @venddate), '-', '')

    END


RETURN @vnumberofdays

END

Answer 1

I would just make another table for Holidays, then you can do a simple DATEDIFF and subtract using that:

DECLARE @start DATETIME;
DECLARE @end DATETIME;

SELECT DATEDIFF(DAY, @start, @end)
       - (SELECT COUNT(DISTINCT HolidayDate)
          FROM Holidays
          WHERE HolidayDate BETWEEN @start AND @end)

I suppose you could also use a table-valued variable for this, but I like having it as a separate table a bit more. It's just cleaner this way. A table-valued function would work too.