create table #Sales ( SiteName nvarchar(1), SaleTime time...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Open Source Meta

Q&A about the site for people organizing, marketing or licensing open source development projects

create table #Sales
( SiteName nvarchar(1),
    SaleTime time,
    Amount decimal,
    BusinessDate Date );

insert into #Sales ( SiteName, SaleTime, Amount, BusinessDate )
values 
( 'A', '7:01:02', 20, '2017-01-02' ),
( 'A', '7:03:22', 25, '2017-01-02' ),
( 'A', '7:05:03', 33, '2017-01-02' ),
( 'A', '7:11:02', 55, '2017-01-02' ),
( 'A', '7:13:05', 46, '2017-01-02' ),
( 'A', '7:17:02', 21, '2017-01-02' ),
( 'A', '8:01:52', 18, '2017-01-02' ),
( 'A', '8:55:42', 7, '2017-01-02' ),
( 'A', '8:56:33', 7, '2017-01-02' ),
( 'A', '8:58:55', 31, '2017-01-02' );



select
        allTimes.TimeStart,
        allTimes.TimeEnd,
        coalesce( count(S.Amount), 0 ) as NumEntries,
        coalesce( sum( S.Amount), 0 ) as SumValues
    from
        ( select
                cast( DateAdd( minute, 15 * (timeSlots.Row -1), '2017-01-01' ) as time ) as TimeStart,
                cast( DateAdd( minute, 15 * timeSlots.Row, '2017-01-01' ) as time ) as TimeEnd
            from
                ( SELECT 
                        ROW_NUMBER() OVER(Order by SaleTime) Row
                    FROM     
                         #Sales) timeSlots
        ) allTimes
            LEFT JOIN #Sales S
                on allTimes.TimeStart <= S.SaleTime
                AND S.SaleTime < allTimes.TimeEnd
            AND ( allTimes.TimeEnd < allTimes.TimeStart
                OR S.SaleTime <= allTimes.TimeEnd )
    group by
        allTimes.TimeStart,
        allTimes.TimeEnd

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results