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

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