IMHO this is a long way around the barn to get what you asked for.
How so?
You want the number of working days between two dates, excluding weekends and holidays. Right? You already know what days are weekends and what days are holidays. Why make it calculate it each time you run the function? What happens in your code if you have a new holiday to add? Let's say you start recognizing presidents day as a holiday in 2018(02/19/2018). You need to change both case statements by adding this date, then you need to increment all of your numbers in your THEN statement after this date. Seems like a lot of work and a big chance for human error. You create a table like I mentioned then you only have to update a few entries in the table. Your function never has to be touched. I know the chances of that happening are slim, again IMHO I think your code makes it harder than it needs to be. I guess I couldn't stay out of it. 🙂
Nah... If I were afraid of a few bumps & bruises or afraid of a little criticism, this is the last place I'd come...
Yes, I started the thread because I had hit a wall and couldn't see a solution... Now that I feel like I have a good solution, I'm looking for someone to see the thing (or things) that I missed and blow my doors off... At the end of the day, I simply want the best solution I can get my hands on.
So, in that vein... If your way is the better way, I will change gears and go that way... But not without hard numbers... My best cold cache number so far is below...
-- SET STATISTICS TIME ON;
-- SET STATISTICS IO OFF;
-- DBCC DROPCLEANBUFFERS;
-- DBCC FREEPROCCACHE;
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 115 ms..
That's damned impressive. Is that on your original million row test table?
Yes it is... All of the times posted up to now has included both the capture of the actual plan and SET STATISTICS IO,TIME ON;
That was the first time with everything turned off except SET STATISTICS TIME ON;
Probably shouldn't exclude parse & compile if there is going to be a distinction between warm and cold cache times...
SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 52 ms.
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 115 ms.
@jeff... I meant to ask you... Would it be a major breach of etiquette if I were to "borrow" the test harness you posted here? https://www.sqlservercentral.com/Forums/FindPost1898722.aspx
I like the output format much better than what I've been using so far.