sql - 缺少某些月份时按年份和日期订购 dt

Question

我有一个简单的查询，用于对日期数据进行分组和排序:

SELECT COUNT(*) AS Events,
       datepart(YYYY, call_date) AS YearPart,
       datepart(MM, call_date) As MonthPart,
       datename(YYYY, call_date) AS YearName,
       dateName(MM, call_date) As MonthName
FROM rm_report
GROUP BY 
    datename(year, call_date), 
    datename(month, call_date),
    datepart(year, call_date), 
    datepart(month, call_date)
ORDER BY 
    YearPart, 
    MonthPart;

问题是第一年的前两个月没有数据。因此，从第三个月开始订购，然后循环到第一个月

EVENTS | YEARPART | YEARNAME | MONTHPART | MONTHNAME
3        2012       2012        3           March
6        2012       2012        4           April
6        2012       2012        5           May
6        2012       2012        6           June
6        2012       2012        7           July
6        2012       2012        8           August
6        2012       2012        9           September
6        2012       2012        10          October
6        2012       2012        11          November
6        2012       2012        12          December
6        2012       2013        1           January
6        2012       2013        2           February

我如何确保如果没有事件，分配 0 以便列表可以正确排序 - 即 MonthPart 列中的第一个条目是“1”，第一个YearPart 列中的条目是 2012 年？

我们正在使用 MS SQL。

Answer 1

您需要先生成月和年的所有组合。然后在 rm_report 上执行 LEFT JOIN:

WITH CteTally(N) AS(
    SELECT N FROM(VALUES
        (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
    )t(N)
),
CteDates(YearPart, MonthPart, YearName, MonthName) AS(
    SELECT
        DATEPART(YEAR, DATEADD(MONTH, t.N - 1, DATEADD(YEAR, a.Y - 1900, 0))),
        DATEPART(MONTH, DATEADD(MONTH, t.N - 1, DATEADD(YEAR, a.Y - 1900, 0))),
        DATENAME(YEAR, DATEADD(MONTH, t.N - 1, DATEADD(YEAR, a.Y - 1900, 0))),
        DATENAME(MONTH, DATEADD(MONTH, t.N - 1, DATEADD(YEAR, a.Y - 1900, 0)))
    FROM(
        SELECT DISTINCT DATEPART(YEAR, call_date) AS Y
        FROM rm_report
    )a
    CROSS JOIN CteTally t
)
SELECT
    COUNT(r.call_date) AS Events,
    d.YearPart,
    d.MonthPart,
    d.YearName,
    d.MonthName
FROM CteDates d
LEFT JOIN rm_report r
    ON d.YearPart = DATEPART(YEAR, r.call_date)
    AND d.YearName= DATENAME(YEAR, r.call_date)
    AND d.MonthPart = DATEPART(MONTH, r.call_date)
    AND d.MonthName = DATENAME(MONTH, r.call_date)
GROUP BY
    d.YearPart, d.YearName, d.MonthPart, d.MonthName
ORDER BY
    d.YearName, d.MonthPart