How can I subtract two dates and exclude if dates in between are a Tuesday or a Holiday?

Question

I have a table with two dates and another table that identifies the day of the week and if that day is a federal holiday. I am going to subtract the two dates from table 1 but I need to be sure that if any day between those two days is identified as a Tuesday from the second table or if it's a holiday, to exclude that from my results. Example, 9/29/2023 & 10/3/2023 should give me a total of 2 days, not 3.

I was thinking a case when statement would work but as I wrote it I realized I needed to check what happened in between the dates so that didn't work.

Any suggestions?

Answer 1

Try this code -

DECLARE @FirstDate DATE = CONVERT(DATE,'9/29/2023',101)
DECLARE @SecondDate DATE = CONVERT(DATE,'10/3/2023',101)

PRINT (DATEDIFF(DD, @FirstDate, @SecondDate)) -(DATEDIFF(wk, @FirstDate,  GETDATE()) * 2) -(CASE WHEN DATENAME(dw, @FirstDate) = 'Tuesday' THEN 1 ELSE 0 END)