SQL Query返回按周，月和年分组的记录。没有记录的周应该返回0 [英] SQL Query to return records grouped by Week, Month, and Year. Weeks with no records should return 0

问题描述

给出以下数据：

Given the following data:

ID      CreatedDate
ID1     2014-06-04 01:40:56.880
ID8     2014-06-05 00:27:02.403
ID6     2014-06-04 01:51:47.060
ID7     2014-06-05 00:25:35.187
ID4     2014-06-04 01:48:44.157
ID10    2014-06-05 00:28:33.993
ID43    2014-06-16 05:17:18.803
ID72    2014-06-20 04:00:07.733
.
.
.
Etc.

我需要编写一个查询按周，月，和一年。需要返回在特定星期内创建的记录数的计数。输出结果如下：

I need to write a query that will group records by week, month, and year. A count of the number of records that were created in a particular week needs to be returned. The output should be as follows:

Week    Month   Year    Count
23         6    2014    16
24         6    2014    0
25         6    2014    7
26         6    2014    0
27         7    2014    25
28         7    2014    18  
.
.
.
etc.

如果一周没有记录，计数应该返回0.只记录是在查询执行后的过去12个月内创建的，应该返回。该查询将每周执行一次以生成报告。

Where a week has no records the count should return 0. Only records that were created within the past 12 months from the time the query is executed should be return. The query will be executed each week to produce a report.

推荐答案

示例目的我将表名称用作[DBO]。 [FACTINTERNETSALES]和列[ORDERDATE]
您的列将为[CreatedDate]。
Fyi，这是在SQL Server中完成的。

Demo purpose I am using the Table name as [DBO].[FACTINTERNETSALES] and the column [ORDERDATE] For you the Column will be [CreatedDate]. Fyi, This is done in SQL Server.

DECLARE @START INT,
        @END INT

SELECT @START   = CAST(CAST(MIN(ORDERDATE) AS DATETIME) AS INT) FROM [DBO].[FACTINTERNETSALES] 
SELECT @END     = CAST(CAST(MAX(ORDERDATE) AS DATETIME) AS INT) FROM [DBO].[FACTINTERNETSALES] 

--SELECT @START,@END

DECLARE @DATEDIM TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, DATED DATETIME)
WHILE @START <= @END
BEGIN
    INSERT INTO @DATEDIM VALUES (CAST(@START AS DATETIME))
    SET @START = @START  + 1 
END

SELECT      LU.WEEK,LU.MONTH,LU.YEAR,ISNULL(MAIN.COUNT,0) [COUNT]
FROM        (
            SELECT      DATEPART(WEEK,DATED) [WEEK],DATEPART(MONTH,DATED) [MONTH],DATEPART(YEAR,DATED) [YEAR] 
            FROM        @DATEDIM
            GROUP BY    DATEPART(WEEK,DATED),DATEPART(MONTH,DATED),DATEPART(YEAR,DATED)) LU
LEFT JOIN
            (
            SELECT  [YEAR],[MONTH],[WEEK],COUNT(*) [COUNT] FROM
            (SELECT DATEPART(YEAR,ORDERDATE) [YEAR],
                    DATEPART(MONTH,ORDERDATE) [MONTH],
                    DATEPART(WEEK,ORDERDATE) [WEEK]
            FROM    [DBO].[FACTINTERNETSALES] ) LUINNER
            GROUP BY [YEAR],[MONTH],[WEEK]) MAIN 
ON
            LU.MONTH = MAIN.MONTH AND
            LU.YEAR = MAIN.YEAR AND
            LU.WEEK = MAIN.WEEK
ORDER BY    LU.YEAR,LU.MONTH,LU.WEEK

结果：（只有结果的一部分）

Result: (Only a portion of result)

这篇关于SQL Query返回按周，月和年分组的记录。没有记录的周应该返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！