Aggregating Schedule Data by Start and End Time

Question

I have a difficult problem that makes me scratch my head a bit. I have some data that represent the delivery of "widgets" for a variable number of days, broken by half an hour.

For example (apologies for formatting - they did not quite understand):

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

I need to generate a query that allows me to group this data into all the individual blocks where I deliver the unit. In the above example, I need to determine only 1 block - 23:00 - 07:00 and the sum of units for this block (160). Therefore, the desired result will be StartTime, EndTime, TotalNoOfUnits.

However, the difficulty arises when we have different delivery options - maybe we have a day when we deliver units within 24 hours.

StartTime, EndTime TotalNoOfUnits, NoOfUnits < > 0.

. , , .

: , 00:00 23:30 , . , .

EDIT2: script, 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)

Answer 2

, . , , .

DECLARE @Table TABLE(
        DateVal DATETIME,
        NoOfUnits INT
)

INSERT INTO @Table SELECT '01 Mar 2010 00:30', 0

INSERT INTO @Table SELECT '01 Mar 2010 23:00', 10
INSERT INTO @Table SELECT '01 Mar 2010 23:30', 10
INSERT INTO @Table SELECT '02 Mar 2010 00:30', 10

INSERT INTO @Table SELECT '02 Mar 2010 01:00', 0

INSERT INTO @Table SELECT '02 Mar 2010 02:30', 20
INSERT INTO @Table SELECT '02 Mar 2010 03:30', 30
INSERT INTO @Table SELECT '02 Mar 2010 04:30', 40

INSERT INTO @Table SELECT '02 Mar 2010 05:00', 0

SELECT  *
FROM    @Table

;WITH DateValues AS(
        SELECT  t.DateVal,
                t.NoOfUnits,
                MIN(tNext.DateVal) MinDate
        FROM    @Table t LEFT JOIN
                @Table tNext    ON  t.DateVal < tNext.DateVal
                                AND tNext.NoOfUnits = 0
        WHERE   t.NoOfUnits != 0
        GROUP BY t.DateVal,
                t.NoOfUnits
)
, DateRanges AS(
        SELECT  DateVal StartDate,
                NoOfUnits,

            ISNULL((SELECT MAX(DateVal) FROM @Table WHERE DateVal < MinDate), (SELECT MAX(DateVal) FROM @Table)) EndDateEndDate
        FROM    DateValues
)
SELECT  MIN(StartDate) StartDate,
        SUM(NoOfUnits) TotalUnits,
        EndDate
FROM    DateRanges
GROUP BY EndDate

Ouput

StartDate               TotalUnits  EndDate
----------------------- ----------- -----------------------
2010-03-01 23:00:00.000 30          2010-03-02 00:30:00.000
2010-03-02 02:30:00.000 90          2010-03-02 04:30:00.000