如何修改SQL查询以自动选取过去12个月的数据？

问题需求 #

现有SQL查询通过硬编码日期的方式按州统计过去12个月的月度总营收，需要修改为自动选取过去12个月数据，无需每次手动调整日期参数。

示例数据定义 #

DECLARE @ExampleData TABLE (SortCode NVARCHAR(2), LoadDate DATE, Tnet INT);
INSERT INTO @ExampleData (SortCode, LoadDate, Tnet) VALUES
('NY', '2022-02-07', 4), ('CA', '2022-02-07', 5), ('WA', '2022-02-07', 6),
('NY', '2022-03-07', 4), ('CA', '2022-03-07', 5), ('WA', '2022-03-07', 6),
('NY', '2022-04-07', 1), ('CA', '2022-04-07', 2), ('WA', '2022-04-07', 3),
('NY', '2022-05-07', 1), ('CA', '2022-05-07', 2), ('WA', '2022-05-07', 3),
('NY', '2022-06-07', 1), ('CA', '2022-06-07', 2), ('WA', '2022-06-07', 3),
('NY', '2022-07-07', 1), ('CA', '2022-07-07', 2), ('WA', '2022-07-07', 3),
('NY', '2022-08-07', 1), ('CA', '2022-08-07', 2), ('WA', '2022-08-07', 3),
('NY', '2022-09-07', 1), ('CA', '2022-09-07', 2), ('WA', '2022-09-07', 3),
('NY', '2022-10-07', 1), ('CA', '2022-10-07', 2), ('WA', '2022-10-07', 3),
('NY', '2022-11-07', 1), ('CA', '2022-11-07', 2), ('WA', '2022-11-07', 3),
('NY', '2022-12-07', 1), ('CA', '2022-12-07', 2), ('WA', '2022-12-07', 3),
('NY', '2023-01-07', 1), ('CA', '2023-01-07', 2), ('WA', '2023-01-07', 3),
('NY', '2023-02-07', 1), ('CA', '2023-02-07', 2), ('WA', '2023-02-07', 3),
('NY', '2023-03-07', 1), ('CA', '2023-03-07', 2), ('WA', '2023-03-07', 3);

当前硬编码查询 #

SELECT CASE SORTCODE WHEN 'AA' THEN 'Total' ELSE SORTCODE END AS STATE,
  ROUND(SUM(CASE WHEN LOADDATE = '2022-02-07' THEN TNET END),0) AS JAN_TOTALREV,
  ROUND(SUM(CASE WHEN LOADDATE = '2022-03-07' THEN TNET END),0) AS FEB_TOTALREV,
  ROUND(SUM(CASE WHEN LOADDATE = '2022-04-07' THEN TNET END),0) AS MAR_TOTALREV,
  ROUND(SUM(CASE WHEN LOADDATE = '2022-05-07' THEN TNET END),0) AS APR_TOTALREV,
  ROUND(SUM(CASE WHEN LOADDATE = '2022-06-07' THEN TNET END),0) AS MAY_TOTALREV,
  ROUND(SUM(CASE WHEN LOADDATE = '2022-07-07' THEN TNET END),0) AS JUN_TOTALREV,
  ROUND(SUM(CASE WHEN LOADDATE = '2022-08-07' THEN TNET END),0) AS JUL_TOTALREV,
  ROUND(SUM(CASE WHEN LOADDATE = '2022-09-07' THEN TNET END),0) AS AUG_TOTALREV,
  ROUND(SUM(CASE WHEN LOADDATE = '2022-10-07' THEN TNET END),0) AS SEP_TOTALREV,
  ROUND(SUM(CASE WHEN LOADDATE = '2022-11-07' THEN TNET END),0) AS OCT_TOTALREV,
  ROUND(SUM(CASE WHEN LOADDATE = '2022-12-07' THEN TNET END),0) AS NOV_TOTALREV,
  ROUND(SUM(CASE WHEN LOADDATE = '2023-01-07' THEN TNET END),0) AS DEC_TOTALREV
FROM [dbo].[Example]
GROUP BY SORTCODE 

解决方案 #

方案1：静态查询（自动筛选日期，列名固定） #

如果不需要动态生成列名，仅需自动筛选过去12个月的数据，可用日期函数替换硬编码日期，同时通过WHERE子句限定时间范围：

SELECT 
  CASE SORTCODE WHEN 'AA' THEN 'Total' ELSE SORTCODE END AS STATE,
  -- 匹配过去12个月中每个月的月度数据
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, DATEADD(MONTH, -11, GETDATE())) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, DATEADD(MONTH, -11, GETDATE())) THEN TNET END), 0) AS MONTH_1_REV,
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, DATEADD(MONTH, -10, GETDATE())) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, DATEADD(MONTH, -10, GETDATE())) THEN TNET END), 0) AS MONTH_2_REV,
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, DATEADD(MONTH, -9, GETDATE())) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, DATEADD(MONTH, -9, GETDATE())) THEN TNET END), 0) AS MONTH_3_REV,
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, DATEADD(MONTH, -8, GETDATE())) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, DATEADD(MONTH, -8, GETDATE())) THEN TNET END), 0) AS MONTH_4_REV,
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, DATEADD(MONTH, -7, GETDATE())) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, DATEADD(MONTH, -7, GETDATE())) THEN TNET END), 0) AS MONTH_5_REV,
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, DATEADD(MONTH, -6, GETDATE())) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, DATEADD(MONTH, -6, GETDATE())) THEN TNET END), 0) AS MONTH_6_REV,
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, DATEADD(MONTH, -5, GETDATE())) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, DATEADD(MONTH, -5, GETDATE())) THEN TNET END), 0) AS MONTH_7_REV,
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, DATEADD(MONTH, -4, GETDATE())) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, DATEADD(MONTH, -4, GETDATE())) THEN TNET END), 0) AS MONTH_8_REV,
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, DATEADD(MONTH, -3, GETDATE())) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, DATEADD(MONTH, -3, GETDATE())) THEN TNET END), 0) AS MONTH_9_REV,
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, DATEADD(MONTH, -2, GETDATE())) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, DATEADD(MONTH, -2, GETDATE())) THEN TNET END), 0) AS MONTH_10_REV,
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) THEN TNET END), 0) AS MONTH_11_REV,
  ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = DATEPART(YEAR, GETDATE()) 
                  AND DATEPART(MONTH, LoadDate) = DATEPART(MONTH, GETDATE()) THEN TNET END), 0) AS MONTH_12_REV
FROM @ExampleData
-- 筛选过去12个月的完整自然月数据
WHERE LoadDate >= DATEADD(MONTH, -12, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
GROUP BY SORTCODE

方案2：动态SQL（自动生成列和日期条件） #

如果需要列名自动匹配过去12个月的月份+年份（比如FEB_2023_TOTALREV），可以用动态SQL自动生成查询语句，彻底解放手动维护成本：

DECLARE @DynamicSQL NVARCHAR(MAX) = '';
DECLARE @MonthOffset INT = 11;

-- 循环生成每个月的统计列
WHILE @MonthOffset >= 0
BEGIN
  DECLARE @CurrentMonth DATE = DATEADD(MONTH, -@MonthOffset, GETDATE());
  DECLARE @MonthName VARCHAR(3) = FORMAT(@CurrentMonth, 'MMM');
  DECLARE @Year VARCHAR(4) = YEAR(@CurrentMonth);
  DECLARE @ColumnAlias VARCHAR(20) = QUOTENAME(@MonthName + '_' + @Year + '_TOTALREV');

  SET @DynamicSQL += ', ROUND(SUM(CASE WHEN DATEPART(YEAR, LoadDate) = ' + CAST(YEAR(@CurrentMonth) AS VARCHAR(4)) + ' 
                                      AND DATEPART(MONTH, LoadDate) = ' + CAST(MONTH(@CurrentMonth) AS VARCHAR(2)) + ' THEN TNET END), 0) AS ' + @ColumnAlias + CHAR(10);

  SET @MonthOffset -= 1;
END

-- 拼接完整SQL语句
SET @DynamicSQL = 'SELECT CASE SORTCODE WHEN ''AA'' THEN ''Total'' ELSE SORTCODE END AS STATE ' + @DynamicSQL + '
FROM @ExampleData
WHERE LoadDate >= DATEADD(MONTH, -12, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
GROUP BY SORTCODE';

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL, N'@ExampleData TABLE (SortCode NVARCHAR(2), LoadDate DATE, Tnet INT)', @ExampleData = @ExampleData;

关键说明 #

1. 日期筛选逻辑：DATEADD(MONTH, -12, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) 会取当前月份第一天往前推12个月的日期，确保覆盖完整的过去12个自然月数据。
2. 动态SQL优势：无需手动维护列名和日期条件，每次执行都会自动适配最新的12个月，列名会清晰显示对应月份的缩写和年份。
3. 适配不同LoadDate规律：如果LoadDate是当月任意日期而非固定某一天，可将CASE条件改为EOMONTH(LoadDate) = EOMONTH(@CurrentMonth)，匹配整月数据。

内容的提问来源于stack exchange，提问作者Jacob Kaczmarek