sql - 按开始时间和结束时间聚合计划数据

Question

我有一个棘手的问题让我摸不着头脑了一会儿。我有一些数据表示在可变天数内交付“小部件”，分割为半小时时段。

例如(为格式化道歉 - 还没有完全掌握它):

Date          Time    NoOfUnits  
01-Mar-2010   00:00   0  
01-Mar-2010   00:30   0  
01-Mar-2010   01:00   0  
.... (following half hour intervals have NoOfUnits = 0)
01-Mar-2010   23:00   10  
01-Mar-2010   23:30   10
02-Mar-2010   00:00   10
.... (following half hour intervals have NoOfUnits = 1)
02-Mar-2010   07:00   10
02-Mar-2010   07:30   0
.... (following half hour intervals have NoOfUnits = 0)
02-Mar-2010   23:30   0

我需要生成一个查询，以便将这些数据分组到我交付单元的所有不同 block 中。在上面的示例中，我只需要标识 1 个 block - 23:00 到 07:00，以及该 block 的单位总和 (160)。因此，所需的结果将是 StartTime、EndTime、TotalNoOfUnits。

但是，当我们有不同的交付模式时，复杂性就来了——也许我们有一天会 24 小时交付单元。

我需要能够查询上述格式的数据并识别所有唯一的 StartTime、EndTime 和 TotalNoOfUnits 组合，其中 NoOfUnits <> 0。

再次为格式问题和一个有点漫无边际的问题道歉。请提出您需要我澄清的任何问题。

编辑:需要说明的是，数据始终在每天的 00:00 到 23:30 之间运行，并且始终存在每个半小时时段。对于任何给定的数据集，只有天数和每半小时时段的单位可能会有所不同。

EDIT2: 下面是一个脚本，它将用 2 天的计划数据填充一个表。这两天的时间表是一样的。根据我的要求，我希望看到的结果是 13:00、00:00、230。正如您将从下面看到的，我的 SQL 技能不是很好，因此令人头疼!

declare @DayCount int
declare @HalfHourCount int
declare @HH int
declare @CurrentDate datetime
declare @BaseDate datetime
declare @NoOfUnits int

set @HalfHourCount = 48
set @DayCount = 4
set @BaseDate = '1 Jan 1900'

create table #Schedule
(
Date datetime
, Time datetime
, NoOfUnits int
)

while @DayCount > 0
begin
set @CurrentDate = dateadd(dd, @DayCount * -1, CONVERT(VARCHAR(10),GETDATE(),111))
set @HH = @HalfHourCount

while @HH > 0
begin
    if @HH > 24
        set @NoOfUnits = 10
    else
    begin
        if @DayCount = 4 and @HH < 10
            set @NoOfUnits = 10
        else
            set @NoOfUnits = 0
    end

    insert into #Schedule(Date, Time, NoOfUnits)
    values (@CurrentDate, dateadd(mi, @HH / 2.0 * 60, @BaseDate), @NoOfUnits)

    select @HH = @HH - 1
end

set @DayCount = @DayCount - 1
end

预期结果(虽然测试数据应该从 00:00 开始到 23:00，而不是 00:30 到 00:00):

StartTime               TotalUnits  EndDate
----------------------- ----------- -----------------------
1900-01-01 00:30:00.000 90          1900-01-01 04:30:00.000
1900-01-01 12:30:00.000 960         1900-01-02 00:00:00.000

Answer 1

根据您提供的数据，这应该可行。它几乎可以肯定地被简化:

;WITH dateCTE
AS
(
        SELECT * 
               ,date + [time] dt
        FROM #Schedule
)
,seqCTE
AS
(
        SELECT NoOfUnits
               ,dt
               ,ROW_NUMBER() OVER (ORDER BY dt) AS rn
        FROM dateCTE               
)
,recCTE
AS
(
        SELECT NoOfUnits
               ,dt
               ,1 AS SEQUENCE
               ,rn
        FROM seqCTE
        WHERE rn = 1

        UNION ALL

        SELECT s.NoOfUnits
              ,s.dt
              ,CASE WHEN (s.NoOfUnits      > 0 
                          AND  r.NoOfUnits > 0
                         )
                      OR (s.NoOfUnits      = 0 
                          AND  r.NoOfUnits = 0
                         )
                    THEN r.SEQUENCE
                    ELSE r.SEQUENCE + 1
               END
              ,s.rn
        FROM recCTE AS r
        JOIN seqCTE AS s
        ON   s.rn = r.rn + 1
)
,summaryCTE
AS
(
        SELECT RIGHT(CONVERT(varchar(23),MIN(dt),120),8)  AS startTime
               ,RIGHT(CONVERT(varchar(23),MAX(dt),120),8) AS endTime
               ,SUM(NoOfUnits) AS NoOfUnits
        FROM recCTE
        GROUP BY SEQUENCE
        HAVING SUM(NoOfUnits) != 0
)
SELECT startTime
       ,endTime
       ,SUM(NoOfUnits)
FROM summaryCTE  
group by startTime
         ,endTime  
OPTION (MAXRECURSION 0)