在 SQL 的季度报告中包括没有数据的季度

Question

目前，我们正在为指定的标准生成季度报告。数据的输出如下所示

"data":
           [
               {
                   "group": "2014-Apr-2014-Jun",
                   "count": 944
               },
               {
                   "group": "2014-Jan-2014-Mar",
                   "count": 154
               },
               {
                   "group": "2013-Oct-2013-Dec",
                   "count": 895
               }
           ]

我们需要进行更改以包含没有任何数据的季度，输出如下所示

"data":
               [
                   {
                       "group": "2014-Oct-2014-Dec",
                       "count": 0
                   },
                   {
                       "group": "2014-Jul-2014-Sep",
                       "count": 0
                   },
                   {
                       "group": "2014-Apr-2014-Jun",
                       "count": 944
                   },
                   {
                       "group": "2014-Jan-2014-Mar",
                       "count": 154
                   },
                   {
                       "group": "2013-Oct-2013-Dec",
                       "count": 895
                   }
               ]

以下是我用来生成此报告的查询

SELECT cast(year([job].jobdate) AS VARCHAR(4)) + '-' + -- gives year
       cast(left(datename(month, DATEADD(q, DATEDIFF(q, 0, [job].jobdate), 0)), 3) AS VARCHAR(3)) + '-' + -- gives start month of the quarter
       cast(year([job].jobdate) AS VARCHAR(4)) + '-' +  -- gives year
       cast(left(datename(month, dateadd(d, - 1, dateadd(q, DATEDIFF(q, 0, [job].jobdate) + 1, 0))), 3) AS VARCHAR(3)) AS 'group' -- gives end month of the quarter
    ,count([job].bgtjobid) AS 'count'
FROM dbo.job [job] WITH (NOLOCK)
WHERE (
        [job].jobdate >= '2013-10-01'
        AND [job].jobdate <= '2014-12-31'
        )
    AND (1 = 1)
GROUP BY cast(year([job].jobdate) AS VARCHAR(4)) + '-' + 
            cast(left(datename(month, DATEADD(q, DATEDIFF(q, 0, [job].jobdate), 0)), 3) AS VARCHAR(3)) + '-' + 
            cast(year([job].jobdate) AS VARCHAR(4)) + '-' + 
            cast(left(datename(month, dateadd(d, - 1, dateadd(q, DATEDIFF(q, 0, [job].jobdate) + 1, 0))), 3) AS VARCHAR(3))

欢迎使用子查询的

答案，但我真的希望在没有任何子查询的情况下执行此操作，因为它会使我的应用程序中的查询生成器有点混乱

我还为此包含了SQL小提琴。

http://sqlfiddle.com/#!3/a769f/1

在运行上述查询时，我们将得到如下结果

|             GROUP | COUNT |
|-------------------|-------|
| 2013-Oct-2013-Dec |     3 |
| 2014-Apr-2014-Jun |     2 |
| 2014-Jan-2014-Mar |     3 |
| 2014-Jul-2014-Sep |     3 |

由于我没有上一季度的数据，因此现在将其排除在外，我需要进行查询以包含最后一个季度的 0

|             GROUP | COUNT |
|-------------------|-------|
| 2013-Oct-2013-Dec |     3 |
| 2014-Apr-2014-Jun |     2 |
| 2014-Jan-2014-Mar |     3 |
| 2014-Jul-2014-Sep |     3 |
| 2014-Oct-2014-Dec |     0 |

提前感谢！！

Answer 1

只需运行以下代码即可获得所需的结果。此外，如果要更改生成报告的日期范围，请更改@start和@end日期。

DECLARE @start DATETIME, @end DATETIME
SET @start = '2013-10-01'
SET @end = '2014-12-31'
;WITH cte AS 
(
    SELECT dt = DATEADD(DAY, -(DAY(@start) - 1), @start)
    UNION ALL
    SELECT DATEADD(MONTH, 1, dt)
    FROM cte
    WHERE dt < DATEADD(DAY, -(DAY(@end) - 1), @end)
)
SELECT Group1, Sum(count1) AS Count
FROM
(
SELECT cast(year([job].jobdate) AS VARCHAR(4)) + '-' + -- gives year
       cast(left(datename(month, DATEADD(q, DATEDIFF(q, 0, [job].jobdate), 0)), 3) AS VARCHAR(3)) + '-' + -- gives start month of the quarter
       cast(year([job].jobdate) AS VARCHAR(4)) + '-' +  -- gives year
       cast(left(datename(month, dateadd(d, - 1, dateadd(q, DATEDIFF(q, 0, [job].jobdate) + 1, 0))), 3) AS VARCHAR(3)) AS 'group1' -- gives end month of the quarter
    ,count([job].bgtjobid) AS 'count1'
FROM job [job] WITH (NOLOCK)
WHERE (
        [job].jobdate >= @start
        AND [job].jobdate <= @end
        )
    AND (1 = 1)
GROUP BY cast(year([job].jobdate) AS VARCHAR(4)) + '-' + 
            cast(left(datename(month, DATEADD(q, DATEDIFF(q, 0, [job].jobdate), 0)), 3) AS VARCHAR(3)) + '-' + 
            cast(year([job].jobdate) AS VARCHAR(4)) + '-' + 
            cast(left(datename(month, dateadd(d, - 1, dateadd(q, DATEDIFF(q, 0, [job].jobdate) + 1, 0))), 3) AS VARCHAR(3))
UNION
  select distinct cast(year(dt) AS VARCHAR(4)) + '-' + -- gives year
       cast(left(datename(month, DATEADD(q, DATEDIFF(q, 0, dt), 0)), 3) AS VARCHAR(3)) + '-' + -- gives start month of the quarter
       cast(year(dt) AS VARCHAR(4)) + '-' +  -- gives year
       cast(left(datename(month, dateadd(d, - 1, dateadd(q, DATEDIFF(q, 0, dt) + 1, 0))), 3) AS VARCHAR(3)) AS 'group1' -- gives end month of the quarter
    ,0 AS 'count1'
FROM cte
) as A
GROUP BY  Group1

-

-快速演示在这里