Post reply

Trying to speed up this 'GetWorkingDays' function.

  • October 19, 2017 at 10:34 pm

    #1964450

    Alan.B - Thursday, October 19, 2017 8:36 PM

    Here's another one for your tests. I think it's a winner 😉

    IF OBJECT_ID('dbo.workdayCalendar') IS NOT NULL
    DROP TABLE dbo.workdayCalendar;

    -- Wide enough range to cover a posible dates; limiting to the years where you have holiday data
    DECLARE @startdate datetime = '20000101', @enddate datetime = '20300101';

    SELECT caldate = ISNULL(caldate, @startdate)
    INTO dbo.workdayCalendar
    FROM
    ( SELECT dateadd(day, n, @startdate)
    FROM dbo.tfn_tally(datediff(day, @startdate, @enddate),0)
    ) c(caldate)
    LEFT JOIN dbo.holiday h ON c.caldate = h.hdate
    WHERE datepart(weekday,c.caldate) BETWEEN 2 AND 6 AND h.hdate IS NULL;
    GO
    CREATE UNIQUE NONCLUSTERED INDEX uq_nc_workdayCalendar ON dbo.workdayCalendar(caldate);
    GO

    -- Logic for your iTVF:
    DECLARE @startdate datetime = '20170101', @enddate datetime = '20170506';

    SELECT count(*)
    FROM dbo.workdayCalendar
    WHERE caldate >= @startdate AND caldate <= @enddate;

    That's a dead ringer for the one that got the whole ball rolling in the first place (even your calendar table... same date range as the one we use). The cost of the count aggregation snowballs and becomes really expensive, really fast, when it's calculating multiple status changes on multiple reports.
    At the end of the day, even simple little, millisecond count aggs add up no matter how much tuning you do, the only way to get past the cost is to get rid of the counts. That's how we ended up with a 60+M row table in the first place...
    It dawned on us that it was stupid to keep paying for the same set of calculations over and over again, when we could do it once and be done with it... so, we decided to pre-calculate every possible date combination in our calendar table. Now all it has to do is a seek on one of two indexes (one on BegDate and one on EndDate, a pick up the value. It still has to do some calculation, because an adjustment has to be done if the BegDate is a workday or not and if the parameters were passed dates only of date & time, but I was never able to see any impact from those calculations. 
    The reason for wanting to dump it... Holiday edits, that are minor on a normal calendar table, turn into a major ordeal when that date affects 10's of thousands of separate calculations. More than a few and it's easier to simply recalculate the whole thing. 
    Then there's the spark that really lit a fire under me... We have a client that has determined that we need to use THEIR holidays when we co the calculations on their stuff... So, now I have two of them! Which, of course, means it's just a matter of time until there are more...

    I'll post what I have tomorrow... Whether I'm done testing & tweaking or not... Hopefully Jeff will put a horse in the race too... That will bring out all the speed freaks.

  • October 20, 2017 at 6:37 am

    #1964475

    @jason,

    I've seen it in the formulas but asking the questions out loud to make absolutely sure...
    1.  If a time is involved in the dates, it should be ignored, correct?
    2.  If the start and end dates both occur on a workday, they should both be counted as a workday, correct?  In other words, if they're both the same date, it should count as "1" workday rather than "0", correct?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 20, 2017 at 8:26 am

    #1964494

    It's Friday so maybe I'm not thinking about this correctly.  But you are just trying to count the number of workdays between two dates, Correct?  Excluding the holidays you listed and excluding Saturday and Sunday's.
    If you have a table with all of the dates and a filed to indicate a workday or not, 1 = yes and 0 is no.  Then you just sum this field for the date range you pass it.
    I created a temp table, just for my ease of clean up, of the holidays you had.  (#holiday)
    Then I created a table with all of the days between 01/01/2000 and 01/01/2030, just using the range of the holiday's you provided.  This table then has a field called workday, all records are set to a value of 1 initially. 
    (#WorkingDayTable)
    Then I update the #WorkingDayTable, setting all dates that are Saturday or Sunday to 0(zero).

    -- If Saturday or Sundayset to zero
    UPDATE #WorkingDayTable
    SET WorkDay = 0
    WHERE DATEPART(DW, DateField) IN (1, 7)
    ;

    I then used the #holiday table to update the #WorkingDayTable.

    -- If a holiday the set to zero
    UPDATE #WorkingDayTable
    SET WorkDay = 0
    WHERE DateField IN (SELECT Holiday FROM #holiday)
    ;

    Then your function would just sum the WorkDay field for the date range passed.

    SELECT SUM(WorkDay) AS NumberOfWorkingDays
    FROM #WorkingDayTable
    WHERE DateField >= '10/20/2017'
        AND DateField <= '10/20/2017'
    ;
    This will return 1 if they are the same date, assuming that is what you wanted.
    This ran for less than a second for every date range I tested.  Am I missing something?

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • October 20, 2017 at 8:31 am

    #1964496

    below86 - Friday, October 20, 2017 8:26 AM

    It's Friday so maybe I'm not thinking about this correctly.  But you are just trying to count the number of workdays between two dates, Correct?  Excluding the holidays you listed and excluding Saturday and Sunday's.
    If you have a table with all of the dates and a filed to indicate a workday or not, 1 = yes and 0 is no.  Then you just sum this field for the date range you pass it.
    I created a temp table, just for my ease of clean up, of the holidays you had.  (#holiday)
    Then I created a table with all of the days between 01/01/2000 and 01/01/2030, just using the range of the holiday's you provided.  This table then has a field called workday, all records are set to a value of 1 initially. 
    (#WorkingDayTable)
    Then I update the #WorkingDayTable, setting all dates that are Saturday or Sunday to 0(zero).

    -- If Saturday or Sundayset to zero
    UPDATE #WorkingDayTable
    SET WorkDay = 0
    WHERE DATEPART(DW, DateField) IN (1, 7)
    ;

    I then used the #holiday table to update the #WorkingDayTable.

    -- If a holiday the set to zero
    UPDATE #WorkingDayTable
    SET WorkDay = 0
    WHERE DateField IN (SELECT Holiday FROM #holiday)
    ;

    Then your function would just sum the WorkDay field for the date range passed.

    SELECT SUM(WorkDay) AS NumberOfWorkingDays
    FROM #WorkingDayTable
    WHERE DateField >= '10/20/2017'
        AND DateField <= '10/20/2017'
    ;
    This will return 1 if they are the same date, assuming that is what you wanted.
    This ran for less than a second for every date range I tested.  Am I missing something?

    This is pretty similar to what I had in mind.   I had envisioned a rather denormalized but highly indexed DimDate table with values for things like IsHoliday, or IsWorkDay being part of the table and the indexes.   Seems to me that would be lightning fast in comparison to pretty much everything else.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • October 20, 2017 at 10:01 am

    #1964520

    Jeff Moden - Friday, October 20, 2017 6:37 AM

    @jason,

    I've seen it in the formulas but asking the questions out loud to make absolutely sure...
    1.  If a time is involved in the dates, it should be ignored, correct?
    2.  If the start and end dates both occur on a workday, they should both be counted as a workday, correct?  In other words, if they're both the same date, it should count as "1" workday rather than "0", correct?

    @jeff,
    1) Yes... I believe so... It mimics the calendar table based approach shown below...
           SELECT
                COUNT(*) AS WorkingDays
            FROM
                dbo.Calendar c
            WHERE
                c.dt BETWEEN @BegDateTime AND @EndDateTime
                AND c.isWeekday = 1
                AND c.isHoliday = 0;

    2) Yes. Here are two examples that should add clarity...
    /*------------------------
    -- same day
    SELECT
        COUNT(*) AS WorkingDays
    FROM
        dbo.Calendar c
    WHERE
        c.dt BETWEEN '2017-02-01 08:05:22' AND '2017-02-01 22:22:22'
        AND c.isWeekday = 1
        AND c.isHoliday = 0;

    WorkingDays
    -----------
    1
    ------------------------*/
    /*------------------------
    -- spanning midnight
    SELECT
        COUNT(*) AS WorkingDays
    FROM
        dbo.Calendar c
    WHERE
        c.dt BETWEEN '2017-02-01 23:59:59' AND '2017-02-02 00:00:01'
        AND c.isWeekday = 1
        AND c.isHoliday = 0;

    WorkingDays
    -----------
    2
    ------------------------*/

  • October 20, 2017 at 10:28 am

    #1964526

    Jason A. Long - Tuesday, October 17, 2017 11:57 PM

    Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math and there are two of them (Orthodox and Catholic).

    The Ordinal business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

    CREATE TABLE Calendar
    (cal_date DATE NOT NULL PRIMARY KEY,
    ordinal_business_nbr INTEGER NOT NULL,
    ...);

    INSERT INTO Calendar
    VALUES ('2007-04-05', 42);
     ('2007-04-06', 43); -- Good Friday
     ('2007-04-07', 43);
     ('2007-04-08', 43); -- Easter Sunday
     ('2007-04-09', 44);
     ('2007-04-10', 45); -- Tuesday, back to work

    To compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:

    SELECT (C2.ordinal_business_nbr - C1.ordinal_business_nbr -1) AS business_day_cnt
    FROM Calendar AS C1, Calendar AS C2
    WHERE C1.cal_date = '2007-04-05'
     AND C2.cal_date = '2007-04-10';

    This saves having to scan all of the rows within the range to get a sum. The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company. 

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • October 20, 2017 at 10:43 am

    #1964531

    Here's a sligthly faster version than the original one. I'm not sure what happens if we add a proper holidays table.
    I tried by adding a filtered index to a calendar table, but it still used the clustered index.

    CREATE FUNCTION dbo.tfn_GetWorkingDays_S
    /* =============================================================================
    10/17/2017 JL, Created: Completly in memory does not need the WorkingDaysPreCalc
    10/20/2017 LC, Alter calculations 
    ============================================================================= */
    (
      @BegDate DATETIME,
      @EndDate DATETIME
    )
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    SELECT WorkingDays = (((DATEDIFF(dd,@BegDate,@EndDate)) --Start with total number of days including weekends
          - (DATEDIFF(wk,@BegDate,@EndDate)*2) --Subtract 2 days for each full weekend
          + (1-SIGN(DATEDIFF(dd,5,@BegDate)%7)) --If StartDate is a Saturday, Add 1
          - (1-SIGN(DATEDIFF(dd,5,@EndDate)%7)))) --If EndDate is a Saturday, Substract 1
          - ((SELECT
              holidays = COUNT(1)
           FROM
            (VALUES (CAST('20000529' AS DATETIME)),
              ('20000704'), ('20000904'), ('20001123'), ('20001124'), ('20001225'), ('20010101'), ('20010528'), ('20010704'), ('20010903'), ('20011122'),
              ('20011123'), ('20011225'), ('20020101'), ('20020527'), ('20020704'), ('20020902'), ('20021128'), ('20021129'), ('20021225'), ('20030101'),
              ('20030526'), ('20030704'), ('20030901'), ('20031127'), ('20031128'), ('20031225'), ('20040101'), ('20040531'), ('20040705'), ('20040906'),
              ('20041125'), ('20041126'), ('20041224'), ('20041231'), ('20050530'), ('20050704'), ('20050905'), ('20051124'), ('20051125'), ('20051226'),
              ('20060102'), ('20060529'), ('20060704'), ('20060904'), ('20061123'), ('20061124'), ('20061225'), ('20070101'), ('20070528'), ('20070704'),
              ('20070903'), ('20071122'), ('20071123'), ('20071225'), ('20080101'), ('20080526'), ('20080704'), ('20080901'), ('20081127'), ('20081128'),
              ('20081225'), ('20090101'), ('20090525'), ('20090703'), ('20090907'), ('20091126'), ('20091127'), ('20091225'), ('20100101'), ('20100531'),
              ('20100705'), ('20100906'), ('20101125'), ('20101126'), ('20101224'), ('20101231'), ('20110530'), ('20110704'), ('20110905'), ('20111124'),
              ('20111125'), ('20111226'), ('20120102'), ('20120528'), ('20120704'), ('20120903'), ('20121122'), ('20121123'), ('20121225'), ('20130101'),
              ('20130527'), ('20130704'), ('20130902'), ('20131128'), ('20131129'), ('20131225'), ('20140101'), ('20140526'), ('20140704'), ('20140901'),
              ('20141127'), ('20141128'), ('20141225'), ('20150101'), ('20150525'), ('20150703'), ('20150907'), ('20151126'), ('20151127'), ('20151225'),
              ('20160101'), ('20160530'), ('20160704'), ('20160905'), ('20161124'), ('20161125'), ('20161226'), ('20170102'), ('20170529'), ('20170704'),
              ('20170904'), ('20171123'), ('20171124'), ('20171225'), ('20180101'), ('20180528'), ('20180704'), ('20180903'), ('20181122'), ('20181123'),
              ('20181225'), ('20190101'), ('20190527'), ('20190704'), ('20190902'), ('20191128'), ('20191129'), ('20191225'), ('20200101'), ('20200525'),
              ('20200703'), ('20200907'), ('20201126'), ('20201127'), ('20201225'), ('20210101'), ('20210531'), ('20210705'), ('20210906'), ('20211125'),
              ('20211126'), ('20211224'), ('20211231'), ('20220530'), ('20220704'), ('20220905'), ('20221124'), ('20221125'), ('20221226'), ('20230102'),
              ('20230529'), ('20230704'), ('20230904'), ('20231123'), ('20231124'), ('20231225'), ('20240101'), ('20240527'), ('20240704'), ('20240902'),
              ('20241128'), ('20241129'), ('20241225'), ('20250101'), ('20250526'), ('20250704'), ('20250901'), ('20251127'), ('20251128'), ('20251225'),
              ('20260101'), ('20260525'), ('20260703'), ('20260907'), ('20261126'), ('20261127'), ('20261225'), ('20270101'), ('20270531'), ('20270705'),
              ('20270906'), ('20271125'), ('20271126'), ('20271224'), ('20271231'), ('20280529'), ('20280704'), ('20280904'), ('20281123'), ('20281124'),
              ('20281225'), ('20290101'), ('20290528'), ('20290704'), ('20290903'), ('20291122'), ('20291123'), ('20291225')
            ) h (holiday)
            WHERE holiday BETWEEN @BegDate AND @EndDate))

    EDIT: This version does not relies on language or datefirst settings.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 20, 2017 at 10:48 am

    #1964533

    sgmunson - Friday, October 20, 2017 8:31 AM

    below86 - Friday, October 20, 2017 8:26 AM

    It's Friday so maybe I'm not thinking about this correctly.  But you are just trying to count the number of workdays between two dates, Correct?  Excluding the holidays you listed and excluding Saturday and Sunday's.
    If you have a table with all of the dates and a filed to indicate a workday or not, 1 = yes and 0 is no.  Then you just sum this field for the date range you pass it.
    I created a temp table, just for my ease of clean up, of the holidays you had.  (#holiday)
    Then I created a table with all of the days between 01/01/2000 and 01/01/2030, just using the range of the holiday's you provided.  This table then has a field called workday, all records are set to a value of 1 initially. 
    (#WorkingDayTable)
    Then I update the #WorkingDayTable, setting all dates that are Saturday or Sunday to 0(zero).

    -- If Saturday or Sundayset to zero
    UPDATE #WorkingDayTable
    SET WorkDay = 0
    WHERE DATEPART(DW, DateField) IN (1, 7)
    ;

    I then used the #holiday table to update the #WorkingDayTable.

    -- If a holiday the set to zero
    UPDATE #WorkingDayTable
    SET WorkDay = 0
    WHERE DateField IN (SELECT Holiday FROM #holiday)
    ;

    Then your function would just sum the WorkDay field for the date range passed.

    SELECT SUM(WorkDay) AS NumberOfWorkingDays
    FROM #WorkingDayTable
    WHERE DateField >= '10/20/2017'
        AND DateField <= '10/20/2017'
    ;
    This will return 1 if they are the same date, assuming that is what you wanted.
    This ran for less than a second for every date range I tested.  Am I missing something?

    This is pretty similar to what I had in mind.   I had envisioned a rather denormalized but highly indexed DimDate table with values for things like IsHoliday, or IsWorkDay being part of the table and the indexes.   Seems to me that would be lightning fast in comparison to pretty much everything else.

    @below86 & @steve-2,

    I'll say this... the original function was a scalar function so I won't try to make that comparison... That said, switching from scalar to iTVF was the first thing fix that was considered. 
    Yes... There was a substantial improvement by making leap from scalar to iTFV but it still wasn't what we were shooting for. Below is the exact body of function...
    SELECT
        COUNT(*) AS WorkingDays
    FROM
        dbo.Calendar c
    WHERE
        c.dt BETWEEN @BegDate AND @EndDate
        AND c.isWeekday = 1
        AND c.isHoliday = 0;

    In the tests that I did, way back when... the "iTVF / calendar table" version, while much better than the scalar, couldn't come close to touching the the "Pre-Calc / iTVF" version. They weren't in two different leagues, they were playing different sports...
    Here is the "function code for the function in use today... Notice that A) there no aggregation taking place and B) the predicate is two equalities... 

    CREATE FUNCTION dbo.tfn_GetWorkingDays
    /* ============================================================================================================
    09/18/2015 JL, Created to be an inline table function for getting the number of working days between two dates.
    ============================================================================================================= */
    (
        @BegDate DATETIME,
        @EndDate DATETIME
    )
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
        SELECT
            WorkingDays = wdpc.WorkingDays - CASE WHEN @BegDate > CAST(@BegDate AS DATE) THEN wdpc.IsBegDateWorkDate ELSE 0 END
        FROM
            dbo.WorkingDaysPreCalc wdpc
        WHERE
            wdpc.BegDate = CAST(@BegDate AS DATE)
            AND wdpc.EndDate = CAST(@EndDate AS DATE)
            AND @BegDate <= @EndDate;
    GO

    And here is the table & indexes for the "WorkingDaysPreCalc" table..

    fair warning... Neither you nor you server will enjoys the next several hours... (in hindsight, I really should have slip this into set-by-set loop)
    CREATE TABLE dbo.WorkingDaysPreCalc (
        BegDate DATE NOT NULL,
        EndDate DATE NOT NULL,
        WorkingDays INT NOT NULL,
        IsBegDateWorkDate TINYINT NOT NULL,
        IsEndDateWorkDate TINYINT NOT NULL
    ) ON Default_Tables
    WITH (DATA_COMPRESSION = PAGE);
    GO
    ALTER TABLE dbo.WorkingDaysPreCalc
    ADD
        CONSTRAINT pk_WorkingDaysPreCalc_Beg_End
        PRIMARY KEY CLUSTERED (BegDate, EndDate)
        WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE) ON Default_Tables;
    GO
    CREATE UNIQUE NONCLUSTERED INDEX uix_WorkingDaysPreCalc_EndDate_BegDate
        ON dbo.WorkingDaysPreCalc (EndDate, BegDate)
        INCLUDE (WorkingDays, IsBegDateWorkDate, IsEndDateWorkDate)
        WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE) ON Default_Indexes;
    GO

    If you want to give it a go, It code is easy... It's created by do a triangular self join to the calendar table... 

    INSERT dbo.WorkingDaysPreCalc (BegDate, EndDate, WorkingDays, IsBegDateWorkDate, IsEndDateWorkDate)
    SELECT
        BegDate = c1.dt,
        EndDate = c2.dt,
        wd.WorkingDays,
        IsBegDateWorkDate = CASE WHEN c1.isWeekday = 1 AND c1.isHoliday = 0 THEN 1 ELSE 0 END,
        IsEndDateWorkDate = CASE WHEN c2.isWeekday = 1 AND c2.isHoliday = 0 THEN 1 ELSE 0 END
    FROM
        dbo.Calendar c1
        JOIN dbo.Calendar c2
            ON c1.dt <= c2.dt
        CROSS APPLY (
            SELECT
                WorkingDays = COUNT(*)
            FROM
                dbo.Calendar c3
            WHERE
                c3.dt >= c1.dt
                AND c3.dt <= c2.dt
                AND c3.IsWeekend = 0
                AND c3.IsHoliday = 0
            ) wd;
    GO

  • October 20, 2017 at 10:52 am

    #1964536

    Luis Cazares - Friday, October 20, 2017 10:43 AM

    Here's a sligthly faster version than the original one. I'm not sure what happens if we add a proper holidays table.
    I tried by adding a filtered index to a calendar table, but it still used the clustered index.

    CREATE FUNCTION dbo.tfn_GetWorkingDays_S
    /* =============================================================================
    10/17/2017 JL, Created: Completly in memory does not need the WorkingDaysPreCalc
    10/20/2017 LC, Alter calculations 
    ============================================================================= */
    (
      @BegDate DATETIME,
      @EndDate DATETIME
    )
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    SELECT WorkingDays = (((DATEDIFF(dd,@BegDate,@EndDate)) --Start with total number of days including weekends
          - (DATEDIFF(wk,@BegDate,@EndDate)*2) --Subtract 2 days for each full weekend
          + (1-SIGN(DATEDIFF(dd,5,@BegDate)%7)) --If StartDate is a Saturday, Add 1
          - (1-SIGN(DATEDIFF(dd,5,@EndDate)%7)))) --If EndDate is a Saturday, Substract 1
          - ((SELECT
              holidays = COUNT(1)
           FROM
            (VALUES (CAST('20000529' AS DATETIME)),
              ('20000704'), ('20000904'), ('20001123'), ('20001124'), ('20001225'), ('20010101'), ('20010528'), ('20010704'), ('20010903'), ('20011122'),
              ('20011123'), ('20011225'), ('20020101'), ('20020527'), ('20020704'), ('20020902'), ('20021128'), ('20021129'), ('20021225'), ('20030101'),
              ('20030526'), ('20030704'), ('20030901'), ('20031127'), ('20031128'), ('20031225'), ('20040101'), ('20040531'), ('20040705'), ('20040906'),
              ('20041125'), ('20041126'), ('20041224'), ('20041231'), ('20050530'), ('20050704'), ('20050905'), ('20051124'), ('20051125'), ('20051226'),
              ('20060102'), ('20060529'), ('20060704'), ('20060904'), ('20061123'), ('20061124'), ('20061225'), ('20070101'), ('20070528'), ('20070704'),
              ('20070903'), ('20071122'), ('20071123'), ('20071225'), ('20080101'), ('20080526'), ('20080704'), ('20080901'), ('20081127'), ('20081128'),
              ('20081225'), ('20090101'), ('20090525'), ('20090703'), ('20090907'), ('20091126'), ('20091127'), ('20091225'), ('20100101'), ('20100531'),
              ('20100705'), ('20100906'), ('20101125'), ('20101126'), ('20101224'), ('20101231'), ('20110530'), ('20110704'), ('20110905'), ('20111124'),
              ('20111125'), ('20111226'), ('20120102'), ('20120528'), ('20120704'), ('20120903'), ('20121122'), ('20121123'), ('20121225'), ('20130101'),
              ('20130527'), ('20130704'), ('20130902'), ('20131128'), ('20131129'), ('20131225'), ('20140101'), ('20140526'), ('20140704'), ('20140901'),
              ('20141127'), ('20141128'), ('20141225'), ('20150101'), ('20150525'), ('20150703'), ('20150907'), ('20151126'), ('20151127'), ('20151225'),
              ('20160101'), ('20160530'), ('20160704'), ('20160905'), ('20161124'), ('20161125'), ('20161226'), ('20170102'), ('20170529'), ('20170704'),
              ('20170904'), ('20171123'), ('20171124'), ('20171225'), ('20180101'), ('20180528'), ('20180704'), ('20180903'), ('20181122'), ('20181123'),
              ('20181225'), ('20190101'), ('20190527'), ('20190704'), ('20190902'), ('20191128'), ('20191129'), ('20191225'), ('20200101'), ('20200525'),
              ('20200703'), ('20200907'), ('20201126'), ('20201127'), ('20201225'), ('20210101'), ('20210531'), ('20210705'), ('20210906'), ('20211125'),
              ('20211126'), ('20211224'), ('20211231'), ('20220530'), ('20220704'), ('20220905'), ('20221124'), ('20221125'), ('20221226'), ('20230102'),
              ('20230529'), ('20230704'), ('20230904'), ('20231123'), ('20231124'), ('20231225'), ('20240101'), ('20240527'), ('20240704'), ('20240902'),
              ('20241128'), ('20241129'), ('20241225'), ('20250101'), ('20250526'), ('20250704'), ('20250901'), ('20251127'), ('20251128'), ('20251225'),
              ('20260101'), ('20260525'), ('20260703'), ('20260907'), ('20261126'), ('20261127'), ('20261225'), ('20270101'), ('20270531'), ('20270705'),
              ('20270906'), ('20271125'), ('20271126'), ('20271224'), ('20271231'), ('20280529'), ('20280704'), ('20280904'), ('20281123'), ('20281124'),
              ('20281225'), ('20290101'), ('20290528'), ('20290704'), ('20290903'), ('20291122'), ('20291123'), ('20291225')
            ) h (holiday)
            WHERE holiday BETWEEN @BegDate AND @EndDate))

    EDIT: This version does not relies on language or datefirst settings.

    Thank you Louis! I've been so focused on the holiday's portion that I hadn't put devoted too much to the weekends portion. I'll get this incorporated this evening and give a go!

  • October 20, 2017 at 12:11 pm

    #1964562

    All I can say is your original function was doing a count not a sum, and it had two additional checks in the where clause compared to what I was suggesting.  For the date range you show we are talking less than 11,000 rows for total number of days.  This shouldn't take very long to run even without indexes.
    I ran the entire SQL below in less than 2 seconds on my dev server.  A function to just do the SUM shouldn't be any slower.  I added 01/01/2000 and 01/01/2030 to the holiday table.
    SELECT *
    INTO #holiday
    FROM
    (VALUES (CAST('20000529' AS DATE)),
    ('20000704'), ('20000904'), ('20001123'), ('20001124'), ('20001225'), ('20010101'), ('20010528'), ('20010704'), ('20010903'), ('20011122'),
    ('20011123'), ('20011225'), ('20020101'), ('20020527'), ('20020704'), ('20020902'), ('20021128'), ('20021129'), ('20021225'), ('20030101'),
    ('20030526'), ('20030704'), ('20030901'), ('20031127'), ('20031128'), ('20031225'), ('20040101'), ('20040531'), ('20040705'), ('20040906'),
    ('20041125'), ('20041126'), ('20041224'), ('20041231'), ('20050530'), ('20050704'), ('20050905'), ('20051124'), ('20051125'), ('20051226'),
    ('20060102'), ('20060529'), ('20060704'), ('20060904'), ('20061123'), ('20061124'), ('20061225'), ('20070101'), ('20070528'), ('20070704'),
    ('20070903'), ('20071122'), ('20071123'), ('20071225'), ('20080101'), ('20080526'), ('20080704'), ('20080901'), ('20081127'), ('20081128'),
    ('20081225'), ('20090101'), ('20090525'), ('20090703'), ('20090907'), ('20091126'), ('20091127'), ('20091225'), ('20100101'), ('20100531'),
    ('20100705'), ('20100906'), ('20101125'), ('20101126'), ('20101224'), ('20101231'), ('20110530'), ('20110704'), ('20110905'), ('20111124'),
    ('20111125'), ('20111226'), ('20120102'), ('20120528'), ('20120704'), ('20120903'), ('20121122'), ('20121123'), ('20121225'), ('20130101'),
    ('20130527'), ('20130704'), ('20130902'), ('20131128'), ('20131129'), ('20131225'), ('20140101'), ('20140526'), ('20140704'), ('20140901'),
    ('20141127'), ('20141128'), ('20141225'), ('20150101'), ('20150525'), ('20150703'), ('20150907'), ('20151126'), ('20151127'), ('20151225'),
    ('20160101'), ('20160530'), ('20160704'), ('20160905'), ('20161124'), ('20161125'), ('20161226'), ('20170102'), ('20170529'), ('20170704'),
    ('20170904'), ('20171123'), ('20171124'), ('20171225'), ('20180101'), ('20180528'), ('20180704'), ('20180903'), ('20181122'), ('20181123'),
    ('20181225'), ('20190101'), ('20190527'), ('20190704'), ('20190902'), ('20191128'), ('20191129'), ('20191225'), ('20200101'), ('20200525'),
    ('20200703'), ('20200907'), ('20201126'), ('20201127'), ('20201225'), ('20210101'), ('20210531'), ('20210705'), ('20210906'), ('20211125'),
    ('20211126'), ('20211224'), ('20211231'), ('20220530'), ('20220704'), ('20220905'), ('20221124'), ('20221125'), ('20221226'), ('20230102'),
    ('20230529'), ('20230704'), ('20230904'), ('20231123'), ('20231124'), ('20231225'), ('20240101'), ('20240527'), ('20240704'), ('20240902'),
    ('20241128'), ('20241129'), ('20241225'), ('20250101'), ('20250526'), ('20250704'), ('20250901'), ('20251127'), ('20251128'), ('20251225'),
    ('20260101'), ('20260525'), ('20260703'), ('20260907'), ('20261126'), ('20261127'), ('20261225'), ('20270101'), ('20270531'), ('20270705'),
    ('20270906'), ('20271125'), ('20271126'), ('20271224'), ('20271231'), ('20280529'), ('20280704'), ('20280904'), ('20281123'), ('20281124'),
    ('20281225'), ('20290101'), ('20290528'), ('20290704'), ('20290903'), ('20291122'), ('20291123'), ('20291225'), ('20000101'), ('20300101')
    ) h (holiday);

    -- DROP TABLE #WorkingDayTable
    DECLARE @date AS DATE;

    SET @date = '01/01/2000';

    SELECT @date AS DateField, CAST(1 AS INT) AS WorkDay
    INTO #WorkingDayTable
    ;

    LOOPHERE:

    SET @date = DATEADD(DD, 1, @date);

    INSERT INTO #WorkingDayTable
    SELECT @date AS DateField, CAST(1 AS INT) AS WorkDay
    ;

    IF @date < '01/01/2030'
        GOTO LOOPHERE;

    --SELECT COUNT(*)
    --FROM #WorkingDayTable
    --;

    -- If Saturday or Sundayset to zero
    UPDATE #WorkingDayTable
    SET WorkDay = 0
    WHERE DATEPART(DW, DateField) IN (1, 7)
    ;

    -- If a holiday the set to zero
    UPDATE #WorkingDayTable
    SET WorkDay = 0
    WHERE DateField IN (SELECT Holiday FROM #holiday)
    ;
    -- the function would just run this code and #WorkingDayTable would be a real table instead of a temp table.
    SELECT SUM(WorkDay) AS NumberOfWorkingDays
    FROM #WorkingDayTable
    WHERE DateField >= '01/01/2000'
        AND DateField <= '01/01/2030'
    This has reached the max of my knowledge, so I'll drop out of this conversation.  Good luck in getting it to run faster.

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • October 20, 2017 at 12:18 pm

    #1964566

    jcelko212 32090 - Friday, October 20, 2017 10:28 AM

    Jason A. Long - Tuesday, October 17, 2017 11:57 PM

    Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math and there are two of them (Orthodox and Catholic).

    The Ordinal business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

    CREATE TABLE Calendar
    (cal_date DATE NOT NULL PRIMARY KEY,
    ordinal_business_nbr INTEGER NOT NULL,
    ...);

    INSERT INTO Calendar
    VALUES ('2007-04-05', 42);
     ('2007-04-06', 43); -- Good Friday
     ('2007-04-07', 43);
     ('2007-04-08', 43); -- Easter Sunday
     ('2007-04-09', 44);
     ('2007-04-10', 45); -- Tuesday, back to work

    To compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:

    SELECT (C2.ordinal_business_nbr - C1.ordinal_business_nbr -1) AS business_day_cnt
    FROM Calendar AS C1, Calendar AS C2
    WHERE C1.cal_date = '2007-04-05'
     AND C2.cal_date = '2007-04-10';

    This saves having to scan all of the rows within the range to get a sum. The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company. 

    Joe, We're thinking along the same lines... and you're dead on the money with the ordinal positions... That was, in fact the "Eureka"  moment that made it all come together. 
    The implementation is where we differ... Turns out I was able to dump both the table constructor (VALUES table)  AND any reference to an external table, by using simple CASE expressions... :w00t:

           cte_holiday (f, l) AS (
                SELECT
                    CASE
                        WHEN '2000-05-29' >= @BegDate THEN 0
                        WHEN '2000-07-04' >= @BegDate THEN 1
                        WHEN '2000-09-04' >= @BegDate THEN 2
                        WHEN '2000-11-23' >= @BegDate THEN 3
                        WHEN '2000-11-24' >= @BegDate THEN 4
                        WHEN '2000-12-25' >= @BegDate THEN 5
                        --....
                        WHEN '2029-07-04' >= @BegDate THEN 204
                        WHEN '2029-09-03' >= @BegDate THEN 205
                        WHEN '2029-11-22' >= @BegDate THEN 206
                        WHEN '2029-11-23' >= @BegDate THEN 207
                        WHEN '2029-12-25' >= @BegDate THEN 208
                    END,
                     CASE
                        WHEN '2000-05-29' >= @EndDate THEN 0
                        WHEN '2000-07-04' >= @EndDate THEN 1
                        WHEN '2000-09-04' >= @EndDate THEN 2
                        WHEN '2000-11-23' >= @EndDate THEN 3
                        WHEN '2000-11-24' >= @EndDate THEN 4
                        WHEN '2000-12-25' >= @EndDate THEN 5
                        --...
                        WHEN '2029-07-04' >= @EndDate THEN 204
                        WHEN '2029-09-03' >= @EndDate THEN 205
                        WHEN '2029-11-22' >= @EndDate THEN 206
                        WHEN '2029-11-23' >= @EndDate THEN 207
                        WHEN '2029-12-25' >= @EndDate THEN 208
                    END
                )
        SELECT
            WorkingDays = DATEDIFF(DAY, DATEADD(DAY, DATEDIFF(DAY, 0, @BegDate), 0), DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate), 0)) - (w.weekend_days + (h.l - h.f))
        FROM
            cte_weekend_count w
            JOIN cte_holiday h
                ON 1 = 1;

    The net result...  No joins, no Cartesian product and and the following execution plan...



    My concern has now shifted. Now I'm concerned that the optimizer is underestimating the cost...
    The plan w/ the new function isn't far too off from the same table being run alone...  but... the execution times beg to differ...

  • October 20, 2017 at 12:25 pm

    #1964568

    Jason A. Long - Friday, October 20, 2017 12:18 PM

    The plan w/ the new function isn't far too off from the same table being run alone...  but... the execution times beg to differ...

    Can you share the differences in the execution times? I'm assuming there's an improvement.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 20, 2017 at 12:30 pm

    #1964569

    Luis Cazares - Friday, October 20, 2017 12:25 PM

    Jason A. Long - Friday, October 20, 2017 12:18 PM

    The plan w/ the new function isn't far too off from the same table being run alone...  but... the execution times beg to differ...

    Can you share the differences in the execution times? I'm assuming there's an improvement.

    Just under 3 seconds w/ the function. 1/2 second as a bare table.

  • October 20, 2017 at 12:37 pm

    #1964571

    I don't think there's any reason to keep sitting on what I have as of right now. I haven't had a chance to plug Loius's weekend calculation yet. But it will happen today. I'm anxious to see if it makes a meaningful impact.
    It also needs inline comments... I'll get those added today as well...

    CREATE FUNCTION dbo.tfn_GetWorkingDays_X2
    /* =============================================================================
    10/18/2017 JL, Created: Completly in memory does not need the WorkingDaysPreCalc    
    ============================================================================= */
    (
        @BegDate DATETIME,
        @EndDate DATETIME
    )
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN

        WITH
            cte_weekend_count AS (
                SELECT
                    weekend_days = CASE
                        WHEN dp.beg_daywk = 1 AND dp.end_daywk = 7 THEN (dp.weeks_diff * 2) + 1
                        WHEN dp.beg_daywk = 7 AND dp.end_daywk = 7 THEN (dp.weeks_diff * 2)
                        WHEN dp.beg_daywk = 7 THEN (dp.weeks_diff * 2) - 1
                        WHEN dp.end_daywk = 7 THEN (dp.weeks_diff * 2) + 1
                        ELSE dp.weeks_diff * 2
                    END
                    FROM
                ( VALUES
                    (    DATEDIFF(WEEK, DATEADD(DAY, DATEDIFF(DAY, 0, @BegDate), 0),
                        DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate), 0)),
                        DATEPART(dw, @BegDate),
                        DATEPART(dw, @EndDate)
                    )
                    ) dp ( weeks_diff, beg_daywk, end_daywk )
                ),
            cte_holiday (f, l) AS (
                SELECT
                    CASE
                        WHEN '2000-05-29' >= @BegDate THEN 0 WHEN '2000-07-04' >= @BegDate THEN 1 WHEN '2000-09-04' >= @BegDate THEN 2 WHEN '2000-11-23' >= @BegDate THEN 3 WHEN '2000-11-24' >= @BegDate THEN 4 WHEN '2000-12-25' >= @BegDate THEN 5
                        WHEN '2001-01-01' >= @BegDate THEN 6 WHEN '2001-05-28' >= @BegDate THEN 7 WHEN '2001-07-04' >= @BegDate THEN 8 WHEN '2001-09-03' >= @BegDate THEN 9 WHEN '2001-11-22' >= @BegDate THEN 10 WHEN '2001-11-23' >= @BegDate THEN 11
                        WHEN '2001-12-25' >= @BegDate THEN 12 WHEN '2002-01-01' >= @BegDate THEN 13 WHEN '2002-05-27' >= @BegDate THEN 14 WHEN '2002-07-04' >= @BegDate THEN 15 WHEN '2002-09-02' >= @BegDate THEN 16 WHEN '2002-11-28' >= @BegDate THEN 17
                        WHEN '2002-11-29' >= @BegDate THEN 18 WHEN '2002-12-25' >= @BegDate THEN 19 WHEN '2003-01-01' >= @BegDate THEN 20 WHEN '2003-05-26' >= @BegDate THEN 21 WHEN '2003-07-04' >= @BegDate THEN 22 WHEN '2003-09-01' >= @BegDate THEN 23
                        WHEN '2003-11-27' >= @BegDate THEN 24 WHEN '2003-11-28' >= @BegDate THEN 25 WHEN '2003-12-25' >= @BegDate THEN 26 WHEN '2004-01-01' >= @BegDate THEN 27 WHEN '2004-05-31' >= @BegDate THEN 28 WHEN '2004-07-05' >= @BegDate THEN 29
                        WHEN '2004-09-06' >= @BegDate THEN 30 WHEN '2004-11-25' >= @BegDate THEN 31 WHEN '2004-11-26' >= @BegDate THEN 32 WHEN '2004-12-24' >= @BegDate THEN 33 WHEN '2004-12-31' >= @BegDate THEN 34 WHEN '2005-05-30' >= @BegDate THEN 35
                        WHEN '2005-07-04' >= @BegDate THEN 36 WHEN '2005-09-05' >= @BegDate THEN 37 WHEN '2005-11-24' >= @BegDate THEN 38 WHEN '2005-11-25' >= @BegDate THEN 39 WHEN '2005-12-26' >= @BegDate THEN 40 WHEN '2006-01-02' >= @BegDate THEN 41
                        WHEN '2006-05-29' >= @BegDate THEN 42 WHEN '2006-07-04' >= @BegDate THEN 43 WHEN '2006-09-04' >= @BegDate THEN 44 WHEN '2006-11-23' >= @BegDate THEN 45 WHEN '2006-11-24' >= @BegDate THEN 46 WHEN '2006-12-25' >= @BegDate THEN 47
                        WHEN '2007-01-01' >= @BegDate THEN 48 WHEN '2007-05-28' >= @BegDate THEN 49 WHEN '2007-07-04' >= @BegDate THEN 50 WHEN '2007-09-03' >= @BegDate THEN 51 WHEN '2007-11-22' >= @BegDate THEN 52 WHEN '2007-11-23' >= @BegDate THEN 53
                        WHEN '2007-12-25' >= @BegDate THEN 54 WHEN '2008-01-01' >= @BegDate THEN 55 WHEN '2008-05-26' >= @BegDate THEN 56 WHEN '2008-07-04' >= @BegDate THEN 57 WHEN '2008-09-01' >= @BegDate THEN 58 WHEN '2008-11-27' >= @BegDate THEN 59
                        WHEN '2008-11-28' >= @BegDate THEN 60 WHEN '2008-12-25' >= @BegDate THEN 61 WHEN '2009-01-01' >= @BegDate THEN 62 WHEN '2009-05-25' >= @BegDate THEN 63 WHEN '2009-07-03' >= @BegDate THEN 64 WHEN '2009-09-07' >= @BegDate THEN 65
                        WHEN '2009-11-26' >= @BegDate THEN 66 WHEN '2009-11-27' >= @BegDate THEN 67 WHEN '2009-12-25' >= @BegDate THEN 68 WHEN '2010-01-01' >= @BegDate THEN 69 WHEN '2010-05-31' >= @BegDate THEN 70 WHEN '2010-07-05' >= @BegDate THEN 71
                        WHEN '2010-09-06' >= @BegDate THEN 72 WHEN '2010-11-25' >= @BegDate THEN 73 WHEN '2010-11-26' >= @BegDate THEN 74 WHEN '2010-12-24' >= @BegDate THEN 75 WHEN '2010-12-31' >= @BegDate THEN 76 WHEN '2011-05-30' >= @BegDate THEN 77
                        WHEN '2011-07-04' >= @BegDate THEN 78 WHEN '2011-09-05' >= @BegDate THEN 79 WHEN '2011-11-24' >= @BegDate THEN 80 WHEN '2011-11-25' >= @BegDate THEN 81 WHEN '2011-12-26' >= @BegDate THEN 82 WHEN '2012-01-02' >= @BegDate THEN 83
                        WHEN '2012-05-28' >= @BegDate THEN 84 WHEN '2012-07-04' >= @BegDate THEN 85 WHEN '2012-09-03' >= @BegDate THEN 86 WHEN '2012-11-22' >= @BegDate THEN 87 WHEN '2012-11-23' >= @BegDate THEN 88 WHEN '2012-12-25' >= @BegDate THEN 89
                        WHEN '2013-01-01' >= @BegDate THEN 90 WHEN '2013-05-27' >= @BegDate THEN 91 WHEN '2013-07-04' >= @BegDate THEN 92 WHEN '2013-09-02' >= @BegDate THEN 93 WHEN '2013-11-28' >= @BegDate THEN 94 WHEN '2013-11-29' >= @BegDate THEN 95
                        WHEN '2013-12-25' >= @BegDate THEN 96 WHEN '2014-01-01' >= @BegDate THEN 97 WHEN '2014-05-26' >= @BegDate THEN 98 WHEN '2014-07-04' >= @BegDate THEN 99 WHEN '2014-09-01' >= @BegDate THEN 100 WHEN '2014-11-27' >= @BegDate THEN 101
                        WHEN '2014-11-28' >= @BegDate THEN 102 WHEN '2014-12-25' >= @BegDate THEN 103 WHEN '2015-01-01' >= @BegDate THEN 104 WHEN '2015-05-25' >= @BegDate THEN 105 WHEN '2015-07-03' >= @BegDate THEN 106 WHEN '2015-09-07' >= @BegDate THEN 107
                        WHEN '2015-11-26' >= @BegDate THEN 108 WHEN '2015-11-27' >= @BegDate THEN 109 WHEN '2015-12-25' >= @BegDate THEN 110 WHEN '2016-01-01' >= @BegDate THEN 111 WHEN '2016-05-30' >= @BegDate THEN 112 WHEN '2016-07-04' >= @BegDate THEN 113
                        WHEN '2016-09-05' >= @BegDate THEN 114 WHEN '2016-11-24' >= @BegDate THEN 115 WHEN '2016-11-25' >= @BegDate THEN 116 WHEN '2016-12-26' >= @BegDate THEN 117 WHEN '2017-01-02' >= @BegDate THEN 118 WHEN '2017-05-29' >= @BegDate THEN 119
                        WHEN '2017-07-04' >= @BegDate THEN 120 WHEN '2017-09-04' >= @BegDate THEN 121 WHEN '2017-11-23' >= @BegDate THEN 122 WHEN '2017-11-24' >= @BegDate THEN 123 WHEN '2017-12-25' >= @BegDate THEN 124 WHEN '2018-01-01' >= @BegDate THEN 125
                        WHEN '2018-05-28' >= @BegDate THEN 126 WHEN '2018-07-04' >= @BegDate THEN 127 WHEN '2018-09-03' >= @BegDate THEN 128 WHEN '2018-11-22' >= @BegDate THEN 129 WHEN '2018-11-23' >= @BegDate THEN 130 WHEN '2018-12-25' >= @BegDate THEN 131
                        WHEN '2019-01-01' >= @BegDate THEN 132 WHEN '2019-05-27' >= @BegDate THEN 133 WHEN '2019-07-04' >= @BegDate THEN 134 WHEN '2019-09-02' >= @BegDate THEN 135 WHEN '2019-11-28' >= @BegDate THEN 136 WHEN '2019-11-29' >= @BegDate THEN 137
                        WHEN '2019-12-25' >= @BegDate THEN 138 WHEN '2020-01-01' >= @BegDate THEN 139 WHEN '2020-05-25' >= @BegDate THEN 140 WHEN '2020-07-03' >= @BegDate THEN 141 WHEN '2020-09-07' >= @BegDate THEN 142 WHEN '2020-11-26' >= @BegDate THEN 143
                        WHEN '2020-11-27' >= @BegDate THEN 144 WHEN '2020-12-25' >= @BegDate THEN 145 WHEN '2021-01-01' >= @BegDate THEN 146 WHEN '2021-05-31' >= @BegDate THEN 147 WHEN '2021-07-05' >= @BegDate THEN 148 WHEN '2021-09-06' >= @BegDate THEN 149
                        WHEN '2021-11-25' >= @BegDate THEN 150 WHEN '2021-11-26' >= @BegDate THEN 151 WHEN '2021-12-24' >= @BegDate THEN 152 WHEN '2021-12-31' >= @BegDate THEN 153 WHEN '2022-05-30' >= @BegDate THEN 154 WHEN '2022-07-04' >= @BegDate THEN 155
                        WHEN '2022-09-05' >= @BegDate THEN 156 WHEN '2022-11-24' >= @BegDate THEN 157 WHEN '2022-11-25' >= @BegDate THEN 158 WHEN '2022-12-26' >= @BegDate THEN 159 WHEN '2023-01-02' >= @BegDate THEN 160 WHEN '2023-05-29' >= @BegDate THEN 161
                        WHEN '2023-07-04' >= @BegDate THEN 162 WHEN '2023-09-04' >= @BegDate THEN 163 WHEN '2023-11-23' >= @BegDate THEN 164 WHEN '2023-11-24' >= @BegDate THEN 165 WHEN '2023-12-25' >= @BegDate THEN 166 WHEN '2024-01-01' >= @BegDate THEN 167
                        WHEN '2024-05-27' >= @BegDate THEN 168 WHEN '2024-07-04' >= @BegDate THEN 169 WHEN '2024-09-02' >= @BegDate THEN 170 WHEN '2024-11-28' >= @BegDate THEN 171 WHEN '2024-11-29' >= @BegDate THEN 172 WHEN '2024-12-25' >= @BegDate THEN 173
                        WHEN '2025-01-01' >= @BegDate THEN 174 WHEN '2025-05-26' >= @BegDate THEN 175 WHEN '2025-07-04' >= @BegDate THEN 176 WHEN '2025-09-01' >= @BegDate THEN 177 WHEN '2025-11-27' >= @BegDate THEN 178 WHEN '2025-11-28' >= @BegDate THEN 179
                        WHEN '2025-12-25' >= @BegDate THEN 180 WHEN '2026-01-01' >= @BegDate THEN 181 WHEN '2026-05-25' >= @BegDate THEN 182 WHEN '2026-07-03' >= @BegDate THEN 183 WHEN '2026-09-07' >= @BegDate THEN 184 WHEN '2026-11-26' >= @BegDate THEN 185
                        WHEN '2026-11-27' >= @BegDate THEN 186 WHEN '2026-12-25' >= @BegDate THEN 187 WHEN '2027-01-01' >= @BegDate THEN 188 WHEN '2027-05-31' >= @BegDate THEN 189 WHEN '2027-07-05' >= @BegDate THEN 190 WHEN '2027-09-06' >= @BegDate THEN 191
                        WHEN '2027-11-25' >= @BegDate THEN 192 WHEN '2027-11-26' >= @BegDate THEN 193 WHEN '2027-12-24' >= @BegDate THEN 194 WHEN '2027-12-31' >= @BegDate THEN 195 WHEN '2028-05-29' >= @BegDate THEN 196 WHEN '2028-07-04' >= @BegDate THEN 197
                        WHEN '2028-09-04' >= @BegDate THEN 198 WHEN '2028-11-23' >= @BegDate THEN 199 WHEN '2028-11-24' >= @BegDate THEN 200 WHEN '2028-12-25' >= @BegDate THEN 201 WHEN '2029-01-01' >= @BegDate THEN 202 WHEN '2029-05-28' >= @BegDate THEN 203
                        WHEN '2029-07-04' >= @BegDate THEN 204 WHEN '2029-09-03' >= @BegDate THEN 205 WHEN '2029-11-22' >= @BegDate THEN 206 WHEN '2029-11-23' >= @BegDate THEN 207 WHEN '2029-12-25' >= @BegDate THEN 208
                    END,
                    CASE
                        WHEN '2000-05-29' >= @EndDate THEN 0 WHEN '2000-07-04' >= @EndDate THEN 1 WHEN '2000-09-04' >= @EndDate THEN 2 WHEN '2000-11-23' >= @EndDate THEN 3 WHEN '2000-11-24' >= @EndDate THEN 4 WHEN '2000-12-25' >= @EndDate THEN 5
                        WHEN '2001-01-01' >= @EndDate THEN 6 WHEN '2001-05-28' >= @EndDate THEN 7 WHEN '2001-07-04' >= @EndDate THEN 8 WHEN '2001-09-03' >= @EndDate THEN 9 WHEN '2001-11-22' >= @EndDate THEN 10 WHEN '2001-11-23' >= @EndDate THEN 11
                        WHEN '2001-12-25' >= @EndDate THEN 12 WHEN '2002-01-01' >= @EndDate THEN 13 WHEN '2002-05-27' >= @EndDate THEN 14 WHEN '2002-07-04' >= @EndDate THEN 15 WHEN '2002-09-02' >= @EndDate THEN 16 WHEN '2002-11-28' >= @EndDate THEN 17
                        WHEN '2002-11-29' >= @EndDate THEN 18 WHEN '2002-12-25' >= @EndDate THEN 19 WHEN '2003-01-01' >= @EndDate THEN 20 WHEN '2003-05-26' >= @EndDate THEN 21 WHEN '2003-07-04' >= @EndDate THEN 22 WHEN '2003-09-01' >= @EndDate THEN 23
                        WHEN '2003-11-27' >= @EndDate THEN 24 WHEN '2003-11-28' >= @EndDate THEN 25 WHEN '2003-12-25' >= @EndDate THEN 26 WHEN '2004-01-01' >= @EndDate THEN 27 WHEN '2004-05-31' >= @EndDate THEN 28 WHEN '2004-07-05' >= @EndDate THEN 29
                        WHEN '2004-09-06' >= @EndDate THEN 30 WHEN '2004-11-25' >= @EndDate THEN 31 WHEN '2004-11-26' >= @EndDate THEN 32 WHEN '2004-12-24' >= @EndDate THEN 33 WHEN '2004-12-31' >= @EndDate THEN 34 WHEN '2005-05-30' >= @EndDate THEN 35
                        WHEN '2005-07-04' >= @EndDate THEN 36 WHEN '2005-09-05' >= @EndDate THEN 37 WHEN '2005-11-24' >= @EndDate THEN 38 WHEN '2005-11-25' >= @EndDate THEN 39 WHEN '2005-12-26' >= @EndDate THEN 40 WHEN '2006-01-02' >= @EndDate THEN 41
                        WHEN '2006-05-29' >= @EndDate THEN 42 WHEN '2006-07-04' >= @EndDate THEN 43 WHEN '2006-09-04' >= @EndDate THEN 44 WHEN '2006-11-23' >= @EndDate THEN 45 WHEN '2006-11-24' >= @EndDate THEN 46 WHEN '2006-12-25' >= @EndDate THEN 47
                        WHEN '2007-01-01' >= @EndDate THEN 48 WHEN '2007-05-28' >= @EndDate THEN 49 WHEN '2007-07-04' >= @EndDate THEN 50 WHEN '2007-09-03' >= @EndDate THEN 51 WHEN '2007-11-22' >= @EndDate THEN 52 WHEN '2007-11-23' >= @EndDate THEN 53
                        WHEN '2007-12-25' >= @EndDate THEN 54 WHEN '2008-01-01' >= @EndDate THEN 55 WHEN '2008-05-26' >= @EndDate THEN 56 WHEN '2008-07-04' >= @EndDate THEN 57 WHEN '2008-09-01' >= @EndDate THEN 58 WHEN '2008-11-27' >= @EndDate THEN 59
                        WHEN '2008-11-28' >= @EndDate THEN 60 WHEN '2008-12-25' >= @EndDate THEN 61 WHEN '2009-01-01' >= @EndDate THEN 62 WHEN '2009-05-25' >= @EndDate THEN 63 WHEN '2009-07-03' >= @EndDate THEN 64 WHEN '2009-09-07' >= @EndDate THEN 65
                        WHEN '2009-11-26' >= @EndDate THEN 66 WHEN '2009-11-27' >= @EndDate THEN 67 WHEN '2009-12-25' >= @EndDate THEN 68 WHEN '2010-01-01' >= @EndDate THEN 69 WHEN '2010-05-31' >= @EndDate THEN 70 WHEN '2010-07-05' >= @EndDate THEN 71
                        WHEN '2010-09-06' >= @EndDate THEN 72 WHEN '2010-11-25' >= @EndDate THEN 73 WHEN '2010-11-26' >= @EndDate THEN 74 WHEN '2010-12-24' >= @EndDate THEN 75 WHEN '2010-12-31' >= @EndDate THEN 76 WHEN '2011-05-30' >= @EndDate THEN 77
                        WHEN '2011-07-04' >= @EndDate THEN 78 WHEN '2011-09-05' >= @EndDate THEN 79 WHEN '2011-11-24' >= @EndDate THEN 80 WHEN '2011-11-25' >= @EndDate THEN 81 WHEN '2011-12-26' >= @EndDate THEN 82 WHEN '2012-01-02' >= @EndDate THEN 83
                        WHEN '2012-05-28' >= @EndDate THEN 84 WHEN '2012-07-04' >= @EndDate THEN 85 WHEN '2012-09-03' >= @EndDate THEN 86 WHEN '2012-11-22' >= @EndDate THEN 87 WHEN '2012-11-23' >= @EndDate THEN 88 WHEN '2012-12-25' >= @EndDate THEN 89
                        WHEN '2013-01-01' >= @EndDate THEN 90 WHEN '2013-05-27' >= @EndDate THEN 91 WHEN '2013-07-04' >= @EndDate THEN 92 WHEN '2013-09-02' >= @EndDate THEN 93 WHEN '2013-11-28' >= @EndDate THEN 94 WHEN '2013-11-29' >= @EndDate THEN 95
                        WHEN '2013-12-25' >= @EndDate THEN 96 WHEN '2014-01-01' >= @EndDate THEN 97 WHEN '2014-05-26' >= @EndDate THEN 98 WHEN '2014-07-04' >= @EndDate THEN 99 WHEN '2014-09-01' >= @EndDate THEN 100 WHEN '2014-11-27' >= @EndDate THEN 101
                        WHEN '2014-11-28' >= @EndDate THEN 102 WHEN '2014-12-25' >= @EndDate THEN 103 WHEN '2015-01-01' >= @EndDate THEN 104 WHEN '2015-05-25' >= @EndDate THEN 105 WHEN '2015-07-03' >= @EndDate THEN 106 WHEN '2015-09-07' >= @EndDate THEN 107
                        WHEN '2015-11-26' >= @EndDate THEN 108 WHEN '2015-11-27' >= @EndDate THEN 109 WHEN '2015-12-25' >= @EndDate THEN 110 WHEN '2016-01-01' >= @EndDate THEN 111 WHEN '2016-05-30' >= @EndDate THEN 112 WHEN '2016-07-04' >= @EndDate THEN 113
                        WHEN '2016-09-05' >= @EndDate THEN 114 WHEN '2016-11-24' >= @EndDate THEN 115 WHEN '2016-11-25' >= @EndDate THEN 116 WHEN '2016-12-26' >= @EndDate THEN 117 WHEN '2017-01-02' >= @EndDate THEN 118 WHEN '2017-05-29' >= @EndDate THEN 119
                        WHEN '2017-07-04' >= @EndDate THEN 120 WHEN '2017-09-04' >= @EndDate THEN 121 WHEN '2017-11-23' >= @EndDate THEN 122 WHEN '2017-11-24' >= @EndDate THEN 123 WHEN '2017-12-25' >= @EndDate THEN 124 WHEN '2018-01-01' >= @EndDate THEN 125
                        WHEN '2018-05-28' >= @EndDate THEN 126 WHEN '2018-07-04' >= @EndDate THEN 127 WHEN '2018-09-03' >= @EndDate THEN 128 WHEN '2018-11-22' >= @EndDate THEN 129 WHEN '2018-11-23' >= @EndDate THEN 130 WHEN '2018-12-25' >= @EndDate THEN 131
                        WHEN '2019-01-01' >= @EndDate THEN 132 WHEN '2019-05-27' >= @EndDate THEN 133 WHEN '2019-07-04' >= @EndDate THEN 134 WHEN '2019-09-02' >= @EndDate THEN 135 WHEN '2019-11-28' >= @EndDate THEN 136 WHEN '2019-11-29' >= @EndDate THEN 137
                        WHEN '2019-12-25' >= @EndDate THEN 138 WHEN '2020-01-01' >= @EndDate THEN 139 WHEN '2020-05-25' >= @EndDate THEN 140 WHEN '2020-07-03' >= @EndDate THEN 141 WHEN '2020-09-07' >= @EndDate THEN 142 WHEN '2020-11-26' >= @EndDate THEN 143
                        WHEN '2020-11-27' >= @EndDate THEN 144 WHEN '2020-12-25' >= @EndDate THEN 145 WHEN '2021-01-01' >= @EndDate THEN 146 WHEN '2021-05-31' >= @EndDate THEN 147 WHEN '2021-07-05' >= @EndDate THEN 148 WHEN '2021-09-06' >= @EndDate THEN 149
                        WHEN '2021-11-25' >= @EndDate THEN 150 WHEN '2021-11-26' >= @EndDate THEN 151 WHEN '2021-12-24' >= @EndDate THEN 152 WHEN '2021-12-31' >= @EndDate THEN 153 WHEN '2022-05-30' >= @EndDate THEN 154 WHEN '2022-07-04' >= @EndDate THEN 155
                        WHEN '2022-09-05' >= @EndDate THEN 156 WHEN '2022-11-24' >= @EndDate THEN 157 WHEN '2022-11-25' >= @EndDate THEN 158 WHEN '2022-12-26' >= @EndDate THEN 159 WHEN '2023-01-02' >= @EndDate THEN 160 WHEN '2023-05-29' >= @EndDate THEN 161
                        WHEN '2023-07-04' >= @EndDate THEN 162 WHEN '2023-09-04' >= @EndDate THEN 163 WHEN '2023-11-23' >= @EndDate THEN 164 WHEN '2023-11-24' >= @EndDate THEN 165 WHEN '2023-12-25' >= @EndDate THEN 166 WHEN '2024-01-01' >= @EndDate THEN 167
                        WHEN '2024-05-27' >= @EndDate THEN 168 WHEN '2024-07-04' >= @EndDate THEN 169 WHEN '2024-09-02' >= @EndDate THEN 170 WHEN '2024-11-28' >= @EndDate THEN 171 WHEN '2024-11-29' >= @EndDate THEN 172 WHEN '2024-12-25' >= @EndDate THEN 173
                        WHEN '2025-01-01' >= @EndDate THEN 174 WHEN '2025-05-26' >= @EndDate THEN 175 WHEN '2025-07-04' >= @EndDate THEN 176 WHEN '2025-09-01' >= @EndDate THEN 177 WHEN '2025-11-27' >= @EndDate THEN 178 WHEN '2025-11-28' >= @EndDate THEN 179
                        WHEN '2025-12-25' >= @EndDate THEN 180 WHEN '2026-01-01' >= @EndDate THEN 181 WHEN '2026-05-25' >= @EndDate THEN 182 WHEN '2026-07-03' >= @EndDate THEN 183 WHEN '2026-09-07' >= @EndDate THEN 184 WHEN '2026-11-26' >= @EndDate THEN 185
                        WHEN '2026-11-27' >= @EndDate THEN 186 WHEN '2026-12-25' >= @EndDate THEN 187 WHEN '2027-01-01' >= @EndDate THEN 188 WHEN '2027-05-31' >= @EndDate THEN 189 WHEN '2027-07-05' >= @EndDate THEN 190 WHEN '2027-09-06' >= @EndDate THEN 191
                        WHEN '2027-11-25' >= @EndDate THEN 192 WHEN '2027-11-26' >= @EndDate THEN 193 WHEN '2027-12-24' >= @EndDate THEN 194 WHEN '2027-12-31' >= @EndDate THEN 195 WHEN '2028-05-29' >= @EndDate THEN 196 WHEN '2028-07-04' >= @EndDate THEN 197
                        WHEN '2028-09-04' >= @EndDate THEN 198 WHEN '2028-11-23' >= @EndDate THEN 199 WHEN '2028-11-24' >= @EndDate THEN 200 WHEN '2028-12-25' >= @EndDate THEN 201 WHEN '2029-01-01' >= @EndDate THEN 202 WHEN '2029-05-28' >= @EndDate THEN 203
                        WHEN '2029-07-04' >= @EndDate THEN 204 WHEN '2029-09-03' >= @EndDate THEN 205 WHEN '2029-11-22' >= @EndDate THEN 206 WHEN '2029-11-23' >= @EndDate THEN 207 WHEN '2029-12-25' >= @EndDate THEN 208
                    END
                )
        SELECT
            WorkingDays = DATEDIFF(DAY, DATEADD(DAY, DATEDIFF(DAY, 0, @BegDate), 0), DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate), 0)) - (w.weekend_days + (h.l - h.f))
        FROM
            cte_weekend_count w
            JOIN cte_holiday h
                ON 1 = 1;
    GO

  • October 20, 2017 at 12:41 pm

    #1964572

    below86 - Friday, October 20, 2017 12:11 PM

    All I can say is your original function was doing a count not a sum, and it had two additional checks in the where clause compared to what I was suggesting.  For the date range you show we are talking less than 11,000 rows for total number of days.  This shouldn't take very long to run even without indexes.
    I ran the entire SQL below in less than 2 seconds on my dev server.  A function to just do the SUM shouldn't be any slower.  I added 01/01/2000 and 01/01/2030 to the holiday table.
    SELECT *
    INTO #holiday
    FROM
    (VALUES (CAST('20000529' AS DATE)),
    ('20000704'), ('20000904'), ('20001123'), ('20001124'), ('20001225'), ('20010101'), ('20010528'), ('20010704'), ('20010903'), ('20011122'),
    ('20011123'), ('20011225'), ('20020101'), ('20020527'), ('20020704'), ('20020902'), ('20021128'), ('20021129'), ('20021225'), ('20030101'),
    ('20030526'), ('20030704'), ('20030901'), ('20031127'), ('20031128'), ('20031225'), ('20040101'), ('20040531'), ('20040705'), ('20040906'),
    ('20041125'), ('20041126'), ('20041224'), ('20041231'), ('20050530'), ('20050704'), ('20050905'), ('20051124'), ('20051125'), ('20051226'),
    ('20060102'), ('20060529'), ('20060704'), ('20060904'), ('20061123'), ('20061124'), ('20061225'), ('20070101'), ('20070528'), ('20070704'),
    ('20070903'), ('20071122'), ('20071123'), ('20071225'), ('20080101'), ('20080526'), ('20080704'), ('20080901'), ('20081127'), ('20081128'),
    ('20081225'), ('20090101'), ('20090525'), ('20090703'), ('20090907'), ('20091126'), ('20091127'), ('20091225'), ('20100101'), ('20100531'),
    ('20100705'), ('20100906'), ('20101125'), ('20101126'), ('20101224'), ('20101231'), ('20110530'), ('20110704'), ('20110905'), ('20111124'),
    ('20111125'), ('20111226'), ('20120102'), ('20120528'), ('20120704'), ('20120903'), ('20121122'), ('20121123'), ('20121225'), ('20130101'),
    ('20130527'), ('20130704'), ('20130902'), ('20131128'), ('20131129'), ('20131225'), ('20140101'), ('20140526'), ('20140704'), ('20140901'),
    ('20141127'), ('20141128'), ('20141225'), ('20150101'), ('20150525'), ('20150703'), ('20150907'), ('20151126'), ('20151127'), ('20151225'),
    ('20160101'), ('20160530'), ('20160704'), ('20160905'), ('20161124'), ('20161125'), ('20161226'), ('20170102'), ('20170529'), ('20170704'),
    ('20170904'), ('20171123'), ('20171124'), ('20171225'), ('20180101'), ('20180528'), ('20180704'), ('20180903'), ('20181122'), ('20181123'),
    ('20181225'), ('20190101'), ('20190527'), ('20190704'), ('20190902'), ('20191128'), ('20191129'), ('20191225'), ('20200101'), ('20200525'),
    ('20200703'), ('20200907'), ('20201126'), ('20201127'), ('20201225'), ('20210101'), ('20210531'), ('20210705'), ('20210906'), ('20211125'),
    ('20211126'), ('20211224'), ('20211231'), ('20220530'), ('20220704'), ('20220905'), ('20221124'), ('20221125'), ('20221226'), ('20230102'),
    ('20230529'), ('20230704'), ('20230904'), ('20231123'), ('20231124'), ('20231225'), ('20240101'), ('20240527'), ('20240704'), ('20240902'),
    ('20241128'), ('20241129'), ('20241225'), ('20250101'), ('20250526'), ('20250704'), ('20250901'), ('20251127'), ('20251128'), ('20251225'),
    ('20260101'), ('20260525'), ('20260703'), ('20260907'), ('20261126'), ('20261127'), ('20261225'), ('20270101'), ('20270531'), ('20270705'),
    ('20270906'), ('20271125'), ('20271126'), ('20271224'), ('20271231'), ('20280529'), ('20280704'), ('20280904'), ('20281123'), ('20281124'),
    ('20281225'), ('20290101'), ('20290528'), ('20290704'), ('20290903'), ('20291122'), ('20291123'), ('20291225'), ('20000101'), ('20300101')
    ) h (holiday);

    -- DROP TABLE #WorkingDayTable
    DECLARE @date AS DATE;

    SET @date = '01/01/2000';

    SELECT @date AS DateField, CAST(1 AS INT) AS WorkDay
    INTO #WorkingDayTable
    ;

    LOOPHERE:

    SET @date = DATEADD(DD, 1, @date);

    INSERT INTO #WorkingDayTable
    SELECT @date AS DateField, CAST(1 AS INT) AS WorkDay
    ;

    IF @date < '01/01/2030'
        GOTO LOOPHERE;

    --SELECT COUNT(*)
    --FROM #WorkingDayTable
    --;

    -- If Saturday or Sundayset to zero
    UPDATE #WorkingDayTable
    SET WorkDay = 0
    WHERE DATEPART(DW, DateField) IN (1, 7)
    ;

    -- If a holiday the set to zero
    UPDATE #WorkingDayTable
    SET WorkDay = 0
    WHERE DateField IN (SELECT Holiday FROM #holiday)
    ;
    -- the function would just run this code and #WorkingDayTable would be a real table instead of a temp table.
    SELECT SUM(WorkDay) AS NumberOfWorkingDays
    FROM #WorkingDayTable
    WHERE DateField >= '01/01/2000'
        AND DateField <= '01/01/2030'
    This has reached the max of my knowledge, so I'll drop out of this conversation.  Good luck in getting it to run faster.

    Noooo, don't go now... Now comes the fun part...  😉

Viewing 15 posts - 16 through 30 (of 92 total)

You must be logged in to reply to this topic. Login to reply