如何在表中没有记录的情况下获得最后12个月的值

Question

我有一个表，其中PartNumberKey和Depot_ID作为复合主键。它返回以下数据-

要求添加最后12个月(即2018-11至2018-12年)中没有0作为总计的月份列。预期产出应是：

请帮我做一下查询？

Answer 1

从一个数字表开始，然后与dateadd一起使用它来生成一个月表。然后你就可以left join了。

如果没有数字表，则可以在足够大的表上使用ROW_NUMBER。

SELECT TOP (DATEDIFF(month, @month1, @month2)+1)
    DATEADD(month, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1, @month1) AS TheMonth
    FROM dbo.MyTable

您还需要PartNumberKey和Depot_ID值的列表。

DECLARE @month1 date = ‘20171201’, @month2 date = ‘20181101’; -- the semicolon is important!!
WITH months as (
    SELECT TOP (DATEDIFF(month, @month1, @month2)+1)
    DATEADD(month, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1, @month1) AS TheMonth
    FROM dbo.MyTable
),
PartsDepots as (
    SELECT DISTINCT PartNumberKey, Depot_ID
    FROM dbo.MyTable
)
SELECT pd.PartNumberKey, pd.Depot_ID, m.TheMonth, COUNT(*) AS Total
FROM months m
CROSS JOIN PartsDepots pd 
LEFT JOIN dbo.MyTable t
    ON t.TheDate >= m.TheMonth
    AND t.TheDate < DATEADD(month,1,m.TheMonth)
    AND t.PartNumberKey = pd.PartNumberKey
    AND t.Depot_ID = pd.Depot_ID
GROUP BY t.TheMonth, pd.PartNumberKey, pd.Depot_ID
;

Answer 2

假设这张表：

CREATE TABLE #ProductData
(
  PartNumberKey char(5),
  Depot_ID char(2),
  [Month] char(7),
  Total int,
  CONSTRAINT PK_PD PRIMARY KEY (PartNumberKey, Depot_ID)
);

INSERT #ProductData VALUES('1538A','2B','2018-02',17),
                          ('1538B','2A','2017-12',23);

这是一个有趣的问题，因为您既需要确定缺少的月份，也需要为那些缺少的月份填写缺少的键/ID组合。

DECLARE @months    int  = 12, -- up to 100
        @thismonth date = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()),1);

;WITH m AS
(
  SELECT m = 1 UNION ALL SELECT m + 1 FROM m WHERE m < @months
),
months(m) AS
(
  SELECT CONVERT(char(7), DATEADD(MONTH, 1-m, @thismonth))
  FROM m
),
keypairs AS
(
  SELECT PartNumberKey, Depot_ID
    FROM #ProductData
    WHERE [Month] >= CONVERT(char(7), DATEADD(MONTH, -11, @thismonth), 120)
    GROUP BY PartNumberKey, Depot_ID
)
SELECT m.m, p.PartNumberKey, p.Depot_ID, COALESCE(pd.Total,0)
  FROM months AS m
  CROSS JOIN keypairs AS p
  LEFT OUTER JOIN #ProductData AS pd
    ON p.PartNumberKey = pd.PartNumberKey
   AND p.Depot_ID = pd.Depot_ID
   AND m.m = pd.[Month]
ORDER BY p.PartNumberKey, p.Depot_ID, m.m;