按月计算活动系统

Question

我有一个[系统]表，其中包含一个ContractDateEnd字段。如果此值为空，则系统为"活动">

我的目标是按月计算当前年份的系统活动数量

因此，对于每个月，我都必须对一个系统求和，如果 ContractEndDate = NULL 或 Month(ContractEndDate(>= 月份编号

Create  TABLE Systems (
[ID] int,
[ContractStartDate] date,
[ContractEndDate] date)
INSERT INTO Systems ([ID], [ContractStartDate], [ContractEndDate])
VALUES 
(1, '1/1/2018', '08/30/2020'),
(2, '1/1/2019', '05/31/2020'),
(3, '1/6/2020', NULL)

我已经设法按系统 ID 拥有活动月份

ID January February March April May June July August September October November December
1    1        1        1    1    1   1    1     1       0        0        0        0
2    1        1        1    1    1   0    0     0       0        0        0        0
3    1        1        1    1    1   1    1     1       1        1        1        1

但我想有这个：

Month        Total
January       3
February      3
March         3
April         3
May           3
June          2
July          2
August        2
September     1
October       1
November      1
December      1

此解决方案 SQL 按活动日期计数使用联接，但不计算空值。.

我该怎么办？

谢谢 马可

Answer 1

让我们尝试使用此查询：

SELECT Month = DATENAME(MONTH, DATEFROMPARTS(2000, MonthNumber, 1)),
Sum   = SUM(IIF(MonthNumber <= ISNULL(MONTH(Systems.ContractEndDate), 12), 1, 0))
FROM (
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
) AS Months (MonthNumber)
CROSS JOIN #Systems AS Systems
GROUP BY MonthNumber