So I've made the following code in order to remove weekends, however now I need to remove the holidays from the datediff part of the code:

Select 

 ii.initialDateFixed
,hh.holdDateFixed
,(datediff(dd,ii.initialDatefixed,hh.holdDatefixed)   1)
  -(DATEDIFF(wk,ii.initialDatefixed,hh.holdDatefixed)*2)
  -(case when DATENAME(dw, ii.initialDatefixed) = 'Saturday' then 1 else 0 end)
  -(case when DATENAME(dw, ii.initialDatefixed) = 'Sunday' then 1 else 0 end) 

from tempTable

I have the following table on my db called dbo.holidays

Is there a way that I can use this table to remove the holidays in the datediff?

My main table called tempTable looks something like this:

CodePudding user response：

Sure. You can use a correlated subquery for this. I'm not sure how the query you provided is running (i.e. what do the table aliases ii and hh point to?); I'm chalking that up to an incomplete redaction of your actual query. But here's a proof-of-concept that should get you what you need:

use tempdb;
go
drop table if exists #t;
create table #t (
    ID varchar(40) not null,
    start_date datetime2(0),
    end_date datetime2(0)
);

insert into #t values 
    ('sadasfdas234134', '2022-09-03 14:14:32', '2022-09-16 14:14:32'),
    ('dsf3245', '2022-07-12 06:32:12', '2022-07-19 12:12:24');

drop table if exists #holidays;
create table #holidays (
    holidayDate date,
    [description] varchar(100)
)

insert into #holidays values
    ('2022-09-05', 'Labor Day'),
    ('2022-11-24', 'Thanksgiving');

select ID,
    [allDays] = datediff(day, start_date, end_date),
    [nonHolidays] = datediff(day, start_date, end_date) - 
        (
            select count(*) 
            from #holidays 
            where holidayDate between t.start_date and t.end_date
        )
from #t as t;