sql - 按小时显示“打开”记录数的数据透视表

Question

我实际上有一个包含两个重要字段的表。ReadyDate 和 CompleteDate。

我希望我的输出给我 24 列，对应于一天中的一个小时，每列给我仍然“打开”的记录数（定义为一小时结束前的 ReadyDate 但一小时结束后的 CompleteDate）在那个时钟时间。

所以诀窍是一条记录可以出现在零列或多列中。上午 6:05 的 ReadyDate 和上午 9:17 的 CompleteDate 将出现在 6:00a、7:00a 和 8:00a 列上。

所以如果表有以下条目

10-14-2019 06:05   10-14-2019 06:10    (this will populate no columns)
10-14-2019 07:12   10-14-2019 09:30    (this will populate the 7:00 hour and 8:00 hour)
10-14-2019 10:02   10-14-2019 13:55    (this will populate the 10:00 hour, 11:00, 12:00)
10-14-2019 12:50   10-14-2019 15:30    (this will populate the 12:00, 13:00, 14:00)

我希望我的输出有 24 列，其中包含以下数字 0、0、0、0、0、0、0、1、1、0、1、1、2、1、1、0、0、0、 0, 0, 0, 0, 0,0

我不知道如何进行。任何见解将不胜感激

标签： sqlsql-serversql-server-2016

Answer 1

编辑更正了 1 小时的班次

这是一个使用临时计数表的 PIVOT 选项。

计入午夜翻车。

不清楚您是否想要按日期排列。如果没有，删除将是一件小事。

例子

;with cte1 as (
    Select Date  = convert(date,D) 
          ,Item  = datepart(hour,D)
          ,Value = 1
     From  YourTable A
     Cross Apply (
                    Select Top (DateDiff(HOUR,ReadyDate,CompleteDate)+0) 
                           D=DateAdd(HOUR,-1+Row_Number() Over (Order By (Select Null)),ReadyDate) 
                     From  master..spt_values n1,master..spt_values n2
                  ) B
),  cte2 as ( 
    Select Date
          ,B.Item 
          ,B.Value
     From  (Select distinct Date from cte1 ) a
     Cross Join (Select Top 24 Item=-1+Row_Number() Over (Order By (Select NULL)),Value=0 From master..spt_values n1 ) B
)
Select *
 From  (Select * from cte1
        Union All
        Select * from cte2
        ) src
 Pivot (sum(Value) for Item in ([0] ,[1] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7] ,[8] ,[9] ,[10],[11],
                                [12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]) ) pvt

退货