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.