是否从日期差异中删除假日

Question

所以我制作了以下代码来删除周末，但现在我需要从代码的datediff部分删除假期：

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

我的数据库上有下表，名为dbo.holidays

holidayDate	描述
2022-09-05 00:00:00.000	劳动节
2022-11-24 00:00:00.000	感恩节

当然。您可以为此使用相关的子查询。我不确定您提供的查询是如何运行的(即表别名ii和hh指向什么？(；我将其归因于对您实际查询的不完整编辑。但这里有一个概念证明，应该能让你得到你需要的：

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;