MSSQL-根据外部日期范围从两列中计算日期

Question

我有一个简单的表，包含案例编号(ID(、opening_date和end_date，其中end_date的值为空(未完成的案例(。它看起来像这样：

ID  opening_date    end_date 
1   2021-01-04      2021-01-14
2   2021-01-04      2021-01-26
3   2021-01-14      2021-02-15
4   2021-02-01      NULL
5   2021-02-04      2021-02-26
6   2021-02-10      2021-02-15

我正在尝试编写一个选择查询，它将简单地按月/周或天(别介意(显示我，每个月设置了多少个案例(opening_date(，关闭了多少案例(end_date(/周问题是，我不能在过滤器中使用开始日期或结束日期，因为不是opening_date列中的每个日期都在end_date中，反之亦然。它应该是在第一列中作为外部表单独生成的特定日期范围或类似的内容，因此，如果出现既没有开始日期也没有结束日期(以天/周/月为单位(的情况，则应在下面的第一个日期中显示一行带零的行-按天示例的结果：

date    openings    endings
2021-01-01  0   0
2021-01-02  0   0
2021-01-03  0   0
2021-01-04  2   0
2021-01-05  0   0
2021-01-06  0   0
2021-01-07  0   0
2021-01-08  0   0
2021-01-09  0   0
2021-01-10  0   0
2021-01-11  0   0
2021-01-12  0   0
2021-01-13  0   0
2021-01-14  1   1
2021-01-15  0   0
2021-01-16  0   0
2021-01-17  0   0
2021-01-18  0   0
2021-01-19  0   0
2021-01-20  0   0
2021-01-21  0   0
2021-01-22  0   0
2021-01-23  0   0
2021-01-24  0   0
2021-01-25  0   0
2021-01-26  0   1
2021-01-27  0   0
2021-01-28  0   0
2021-01-29  0   0
2021-01-30  0   0
2021-01-31  0   0
2021-02-01  1   0
2021-02-02  0   0
2021-02-03  0   0
2021-02-04  1   0
2021-02-05  0   0
2021-02-06  0   0
2021-02-07  0   0
2021-02-08  0   0
2021-02-09  0   0
2021-02-10  1   0
2021-02-11  0   0
2021-02-12  0   0
2021-02-13  0   0
2021-02-14  0   0
2021-02-15  0   2
2021-02-16  0   0
2021-02-17  0   0
2021-02-18  0   0
2021-02-19  0   0
2021-02-20  0   0
2021-02-21  0   0
2021-02-22  0   0
2021-02-23  0   0
2021-02-24  0   0
2021-02-25  0   0
2021-02-26  0   1
2021-02-27  0   0
2021-02-28  0   0

按月份：

Month   openings endings
2021-01    3       2
2021-02    3       3

请帮帮我。提前谢谢。

Answer 1

您需要一个日历表您从日历开始，然后LEFT JOIN所有其他内容。

为了获得每天的计算结果，我们可以取消透视并分组，然后计算每日总

你可以有一张真正的桌子。或者你可以在飞行中生成它，就像这样：

WITH
L0 AS ( SELECT c = 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),
L1 AS ( SELECT c = 1 FROM L0 A, L0 B, L0 C ),
Nums AS ( SELECT rownum = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM L1 ),
Dates AS ( SELECT [date] = DATEADD(day, rownum, '20180101')
FROM Nums )
SELECT
d.[date],
openings = ISNULL(t.openings, 0),
endings  = ISNULL(t.endings, 0)
FROM Dates d
LEFT JOIN (
SELECT v.AllDates,
openings = COUNT(IsOpen),
endings  = COUNT(IsEnd)
FROM YourTable t
CROSS APPLY (VALUES
(opening_date, 1, NULL),
(end_date,  NULL,    1)
) v(AllDates, IsOpen, IsEnd)
GROUP BY v.AllDates
) t ON t.AllDates = d.[date];