每周组条目，其中每列都得到了特定的日期验证

Question

我尝试在一年中将数据字段X介于该周之间的每周的门票分组。最终结果应该类似于Week，Datefield1，Datefield2..

到目前为止，我得到的结论是：

DECLARE @YearStartDate datetime
SET @YearStartDate = '2016-01-04 00:00:00.000'
DECLARE @YearEndDate datetime
SET @YearEndDate = '2016-04-24 00:00:00.000'

SELECT
Year(T.CreateDate) as 'Year',
'Week ' + cast(datepart(wk, CreateDate) as varchar(2)) as 'Week',
Sum(CASE WHEN CreateDate BETWEEN @YearStartDate and @YearEndDate
    THEN 1 Else 0 End) 'Created' 
FROM mytable AS T
GROUP BY Year(T.CreateDate),datepart(wk,T.CreateDate)
ORDER BY Year(T.CreateDate),datepart(wk, T.CreateDate)

结果：

Year        Week    Created
----------- ------- -----------
2016        Week 1      0
2016        Week 2      5
2016        Week 3      3
2016        Week 4      2

目标：

Year        Week    Created    Schuduled    Closed
----------- ------- ----------- ----------- -----------
2016        Week 1       0          0          0
2016        Week 2       5          3          2
2016        Week 3       3          2          2
2016        Week 4       2          2          0

有什么建议？

SELECT
Year(..) as 'Year',
'Week ' + cast(datepart(wk, ...) as varchar(2)) as 'Week',
Sum(CASE WHEN CreateDate BETWEEN @YearStartDate and @YearEndDate
    THEN 1 Else 0 End) 'Created'
Sum(CASE WHEN ScheduledDate BETWEEN @YearStartDate and @YearEndDate
    THEN 1 Else 0 End) 'Scheduled' 
Sum(CASE WHEN ClosedDate BETWEEN @YearStartDate and @YearEndDate
    THEN 1 Else 0 End) 'Closed' 
FROM mytable AS T
GROUP BY Year(...)
ORDER BY Year(...)

约束条件：

我可以计算一个数据字段每周的条目数，但对于几个数据字段(已创建、已计划、挂起、已关闭)，我认为我需要更改逻辑。可以在同一个表上创建内连接或左连接。

我想知道你是否能给我一些指导，让我找到正确的道路。谢谢你抽出时间来帮助我。万事如意。

Answer 1

对，在其他类似的SELECT上连接预定的和关闭的应该是可行的。像这样的东西

DECLARE @YearStartDate datetime = '2016-01-04 00:00:00.000';
DECLARE @YearEndDate datetime = '2016-04-24 00:00:00.000';

SELECT CLR.*, SCH.Schuduled, CLS.Closed FROM
    (SELECT
        Year(T.CreateDate) as 'Year',
        'Week ' + cast(datepart(wk, CreateDate) as varchar(2)) as 'Week',
        Sum(CASE WHEN CreateDate BETWEEN @YearStartDate and @YearEndDate THEN 1 Else 0 End) 'Created' 
    FROM mytable AS T
    GROUP BY Year(T.CreateDate),datepart(wk,T.CreateDate)
    ) CRT

    JOIN
    (SELECT
        Year(T.ScheduleDate) as 'Year',
        'Week ' + cast(datepart(wk, ScheduleDate) as varchar(2)) as 'Week',
        Sum(CASE WHEN ScheduleDate BETWEEN @YearStartDate and @YearEndDate THEN 1 Else 0 End) 'Schuduled' 
    FROM mytable AS T
    GROUP BY Year(T.ScheduleDate),datepart(wk,T.ScheduleDate)
    ) SCH
    ON CRT.Year = SCH.Year
        AND CRT.Week = SCH.Week

    JOIN
    (SELECT
        Year(T.ClosedDate) as 'Year',
        'Week ' + cast(datepart(wk, ClosedDate) as varchar(2)) as 'Week',
        Sum(CASE WHEN ClosedDate BETWEEN ClosedDate and @YearEndDate THEN 1 Else 0 End) 'Closed' 
    FROM mytable AS T
    GROUP BY Year(T.ClosedDate),datepart(wk,T.ClosedDate)
    ) CLS
    ON CRT.Year = CLS.Year
        AND CRT.Week = CLS.Week
ORDER BY Year, Week

Answer 2

您有三个不同的日期列。一种方法是沿着维度预先聚合，然后使用full outer join。或者，您可以使用union all和聚合：

SELECT Year(dte) as [Year],
       'Week ' + datename(wk, dte) as [Week],
       Sum(Created) as Created,
       Sum(Scheduled) as Scheduled,
       Sum(Closed) as Closed
FROM ((SELECT createddate as dte, 1 as created, 0 as schedule, 0 as closed
       FROM mytable
      ) UNION ALL
      (SELECT scheduleddate as dte, 0 as created, 1 as schedule, 0 as closed
       FROM mytable
      ) UNION ALL
      (SELECT closeddate as date, 1 as created, 0 as schedule, 0 as closed
       FROM mytable
      ) 
     ) t
GROUP BY Year(date), datename(wk, dte)
ORDER BY Year(date), datename(wk, dte);

我会鼓励几件事：

• 不对列别名使用单引号。仅对字符串和日期名称使用单引号。
• 不使用诸如"year“之类的保留字段作为列或表名。

还要注意datename()的使用。这将返回一个字符串，因此不需要进行转换。