SQL Server语言 - 选择过去 12 个月，包括没有记录的月份

Question

>我需要使用Select SQL's for SQLServer创建最近12个月从表中计数的报告。到目前为止，我能够使用各种 Stackoverflow 答案创建以下内容。但是，它并不能让我获得几个月没有数据的输出。 以下是到目前为止对我有帮助的几个链接： MSDN

 (I apologize for any indentation errors(I've tried Ctl+K))

该表有很多列，但我感兴趣的是lastupdatetimestamp。

;WITH CTE_DatesTable
   AS 
(
 SELECT CAST(CURRENT_TIMESTAMP as datetime) AS lastupdatetimestamp
 UNION ALL
 SELECT DATEADD(m, -1, lastupdatetimestamp)
 FROM CTE_DatesTable
 WHERE DATEADD(m, -1, lastupdatetimestamp) > DATEADD(m, -12,CURRENT_TIMESTAMP )
)
SELECT [MONTH] ,
     [LAST_12_MONTHS]
FROM (  
SELECT MONTH(lastupdatetimestamp) AS   MonthNumber,
   DATENAME(MONTH,lastupdatetimestamp) AS [MONTH], 
   COUNT(*) AS [LAST_12_MONTHS]
 FROM FILES 
 GROUP BY MONTH(lastupdatetimestamp), 
    DATENAME(MONTH,lastupdatetimestamp)
    ) AS Data
 RIGHT OUTER JOIN
  (
        SELECT YEAR(lastupdatetimestamp) AS YearNumber
              ,MONTH(lastupdatetimestamp) AS MonthNumber
        FROM CTE_DatesTable
    ) AS DateTable
ON Data.MonthNumber = DateTable.MonthNumber 
ORDER BY DateTable.MonthNumber DESC

这输出：

| MONTH     | LAST_12_MONTHS |
|-----------|--------------- |
|   (null)  |   (null)       |
|   (null)  |   (null)       |
|   (null)  |   (null)       |
|   (null)  |   (null)       |
|   (null)  |   (null)       |
|   (null)  |   (null)       |
|   June    |   45           |
|   May     |   23           |
|   (null)  |   (null)       |
|   (null)  |   (null)       |
|   (null)  |   (null)       |
|   (null)  |   (null)       |

所需输出：

| MONTH     | LAST_12_MONTHS |
|-----------|--------------- |
|   July    |   46           |
|   Aug     |   56           |
|   Sep     |   45           |
|   Oct     |   45           |
|   Nov     |   44           |
|   Dec     |   87           |
|   Jan     |   6            |
|   Feb     |   56           |
|   March   |   664          |
|   April   |   56           |
|   May     |   23           |
|   June    |   45           |

到目前为止，我有几个问题：

1. 没有数据的月份显示为"空"

2. 我正在获得日历月份，而不是过去 12 个月。

   任何帮助都非常感谢。

   谢谢

Answer 1

这是一个解决方案的想法：

DECLARE @Now AS DATE = GETDATE()
;WITH LastTwelveMonth AS (
      SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Now)-1), @Now),101) AS Date_Value
      UNION ALL
      SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(MONTH, -1, Date_Value))-1), @Now),101) AS Date_Value
      FROM LastTwelveMonth
), ValuesForMonth AS (
      SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(lastupdatetimestamp)-1), lastupdatetimestamp),101) AS DateForData, SUM(DataToCount) OVER (PARTITION BY MONTH(lastupdatetimestamp), YEAR(lastupdatetimestamp)) AS DataCounted
      FROM FILES
)
SELECT ISNULL(Files.DataCounted, 0), LastTwelveMonth.Date_Value
FROM LastTwelveMonth
LEFT JOIN ValuesForMonth ON ValuesForMonth.DateForData = LastTwelveMonth.Date_Value