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...