SQL Server select-复杂事例日期语句

Question

我需要写一个select语句，说明dbo.activity.OpenDate和dbo.activity.CloseDate之间的日期范围是否小于3天，然后为1，否则为0。但是，我不能包括非工作日。因此，如果是周末或BH，则会更改计算。所有非工作日和格式均为dbo.date。下表均为。

dbo。日期

	非工作日
2022年1月	sat
2022年5月12日	太阳
2022年9月8日	太阳
2022年4月3日	银行假日

select [open date], [close date],
case when exists (select * from dates d
where d.[date] between a.[open date] and a.[close date])
then 1 else 0 end as NonWorkingDayExists
from activity a;

编辑：重读你修改后的问题：

SELECT OpenDate, CloseDate, CASE WHEN COUNT([holiday].[date])<3 THEN 1 ELSE 0 END AS nwc
FROM activity a
CROSS APPLY(SELECT TOP(DATEDIFF(DAY, a.OpenDate, a.CloseDate)+1)ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
FROM master.sys.all_columns t1
CROSS JOIN master.sys.all_columns t2) t(N)
CROSS APPLY(SELECT DATEADD(DAY, t.N-1, a.OpenDate)) theDates(dt)
OUTER APPLY(SELECT [date] FROM dbo.dates AS d WHERE d.date=theDates.dt) holiday([date])
GROUP BY  OpenDate, CloseDate;

然而，你的约会让我觉得那些阳光明媚的日子可能意味着周末，这是不必要的。你可能会得到它的检查日期：

SELECT activityId, OpenDate, CloseDate, 
CASE WHEN SUM(CASE WHEN wd < 2 OR holiday.[date] IS NOT NULL THEN 1 END) < 3 THEN 1 ELSE 0 END AS nonworking
FROM activity a
CROSS APPLY (
SELECT TOP (DATEDIFF(DAY, a.opendate, a.closedate)+1)
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2) t(N)
CROSS APPLY (SELECT DATEADD(DAY,t.N-1, a.OpenDate)) theDates(dt)
CROSS APPLY (SELECT (DATEPART(WEEKDAY, theDates.dt)+ @@DATEFIRST)%7) weeks(wd)
OUTER APPLY (SELECT [date] FROM dbo.dates AS d WHERE d.date = theDates.dt) holiday([date])
GROUP BY activityId, OpenDate, CloseDate;

我在这里添加了DBFiddle演示。

编辑：我们正在做的事情的解释：

SELECT OpenDate, CloseDate, CASE WHEN COUNT([holiday].[date])<3 THEN 1 ELSE 0 END AS nwc
FROM activity a
CROSS APPLY(SELECT TOP(DATEDIFF(DAY, a.OpenDate, a.CloseDate)+1)ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
FROM master.sys.all_columns t1
CROSS JOIN master.sys.all_columns t2) t(N)
CROSS APPLY(SELECT DATEADD(DAY, t.N-1, a.OpenDate)) theDates(dt)
OUTER APPLY(SELECT [date] FROM dbo.dates AS d WHERE d.date=theDates.dt) holiday([date])
GROUP BY  OpenDate, CloseDate;

让我们仔细分析一下它是如何工作的：

SELECT TOP(DATEDIFF(DAY, a.OpenDate, a.CloseDate)+1) ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
FROM master.sys.all_columns t1
CROSS JOIN master.sys.all_columns t2) t(N)
CROSS APPLY(SELECT DATEADD(DAY, t.N-1, a.OpenDate)

假设一个特定行的OpenDate和CloseDate的日期相差9天，我们有(9+1(10天(包括OpenDate(，SQL的这一部分将返回一个"表"，如：

N

12.3.4.5.6.7.8.910

CROSS APPLY允许我们每行都这样做，所以第一行我们有1,2…114天，第二行95天等等。你可以看到这只执行该部分(在小提琴或你当地的SSMS中(：

SELECT *
FROM activity a
CROSS APPLY (
SELECT TOP (DATEDIFF(DAY, a.opendate, a.closedate)+1)
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2) t(N)

交叉和外部应用是每行运行操作的简单而廉价的方式。下一次交叉应用在上一次的基础上构建并创建日期列(日期(dt((：

SELECT *
FROM activity a
CROSS APPLY (
SELECT TOP (DATEDIFF(DAY, a.opendate, a.closedate)+1)
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2) t(N)
CROSS APPLY (SELECT DATEADD(DAY,t.N-1, a.OpenDate)) theDates(dt)

然后下一次交叉应用在"0"中为工作日添加另一列；先设置日期"；中性方式(所以0总是周六，1是周日，依此类推(。

然后是一个OUTER APPLY将假期添加到上一个：

SELECT *
FROM activity a
CROSS APPLY (
SELECT TOP (DATEDIFF(DAY, a.opendate, a.closedate)+1)
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2) t(N)
CROSS APPLY (SELECT DATEADD(DAY,t.N-1, a.OpenDate)) theDates(dt)
CROSS APPLY (SELECT (DATEPART(WEEKDAY, theDates.dt)+ @@DATEFIRST)%7) weeks(wd)
OUTER APPLY (SELECT [date] FROM dbo.dates AS d WHERE d.date = theDates.dt) holiday([date])

CROSS和OUTER APPLY之间的区别在于，CROSS APPLY类似于内部联接，需要匹配，外部应用类似于左联接，不需要匹配，当没有匹配时列值为null。

如果你运行它，你会看到我们正在使用的"表"。剩下的就是做一个简单的聚合，通过分组。它处于两个SQL的最终状态。

在其中一个案例中，我们只关心日期表中有匹配日期的日期，因此：

COUNT([holiday].[date])

我们所需要的就是检查它，并将其放入一个小于3的case语句中。这是有效的，因为holiday.date为NULL的行将不被计算在内。

如果我们需要检查日期是周六、周日(分别为0和1个工作日(还是日期表中的日期，那么我们会这样做：

SUM(CASE WHEN wd < 2 OR holiday.[date] IS NOT NULL THEN 1 END)

用于计数匹配的行，并在小于3时与case语句进行比较。

我希望现在已经清楚了。

这个DBFiddle显示了步骤。但要小心，你需要滚动才能看到所有内容。

Answer 1

如果我理解你是正确的，如果营业日期和结束日期之间的业务天数(而不仅仅是任何日历天(少于3，你就想分配一个1。假设我说得对，你能不能从差异中减去营业日期和结束日期之间的非工作日数，以确保它计入非工作日？

select a.opendate, 
a.closedate, 
case when datediff(day, a.opendate, a.closedate)-count(b.nonworkingday) <3 then 1 else 0 end as flag
from activity a
left join dates b on b.date between a.opendate and a.closedate
group by a.opendate, a.closedate

Answer 2

我们可以在这里使用左反连接方法：

SELECT DISTINCT a.[open date], a.[close date],
CASE WHEN d.date IS NULL THEN 0 ELSE 1 END AS flag
FROM activity a
LEFT JOIN dates d
ON d.date BETWEEN a.[open date] AND a.[close date];