Group sales data by time interval for every 15 min for one day

Question

I have a sales table and it contains sales figure by different store along with timing, let's say in one day and one of store we have done 10,000 transactions then I need to find the total sales for every 15 min for that particular business date, keeping in mind for example: if there's no sales between 12:00 PM to 12:15 PM then it should be zero as a value or null.

In a day we have 24 hours so it means 96 columns for the 15 min interval.

Sales Table:

SiteName          Time          Amount        BusinessDate
----------------------------------------------------------
A                7:01:02 AM     20            2017-01-02
A                7:03:22 AM     25            2017-01-02
A                7:05:03 AM     33            2017-01-02
A                7:11:02 AM     55            2017-01-02
A                7:13:05 AM     46            2017-01-02
A                7:17:02 AM     21            2017-01-02
A                8:01:52 AM     18            2017-01-02
A                8:55:42 AM     7             2017-01-02
A                8:56:33 AM     7             2017-01-02
A                8:58:55 AM     31            2017-01-02

and so on

How can I accomplish this?!

I would use a tally table for this. If you want more details you will need to provide them to us. Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, May 30 '17 at 20:53
You will need to create time interval table either up-front or as recursive query in CTE and then join on it. — PM 77-1, May 30 '17 at 20:54
@PM77-1 I would really be thankful if you could provide me a high level syntax just to be sure how am going to start — Omran Moh'd, May 30 '17 at 20:59
@SeanLange I have provided sample data on how my table looks like — Omran Moh'd, May 30 '17 at 21:07
Syntax isn't a high level thing. Syntax is very specific. I would use a tally table here and then aggregate your data into groups of 15 minute intervals. Do you really have date and time as separate columns? And are they date and time datatypes? — Sean Lange, May 30 '17 at 21:09
@vkp Yes I do, sorry I didn't select it from my query while I provided sample info — Omran Moh'd, May 30 '17 at 21:09
@SeanLange thanks for the info, yes i have time and date as separate columns and both are in proper datatype. — Omran Moh'd, May 30 '17 at 21:10
Why time and date is in different columns? I would be much better to play on the same column, instead of two columns... — Maciej Los, May 30 '17 at 21:13
@MaciejLos this is how my table structure is, but if you think it's better to do so then I would create a computed column — Omran Moh'd, May 30 '17 at 21:15
Have a look at https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example for the approach. — PM 77-1, May 30 '17 at 21:22

John Cappelletti · Accepted Answer · 2017-05-30T21:57:13.773

Dynamic Example

Declare @SQL varchar(max) = Stuff((Select ',' + QuoteName(T) 
                                    From (Select Top 96 T=format(DateAdd(Minute,(Row_Number() Over (Order By (Select null))-1)*15,0),'HH:mm') From  master..spt_values n1) A
                                    Order by 1 
                                    For XML Path('')),1,1,'') 
Select  @SQL = '
Select *
From (
        Select [SiteName]
              ,Col   = format(DateAdd(MINUTE,(DatePart(HOUR,[Time])*60) + ((DatePart(MINUTE,[Time]) / 15)*15),0),''HH:mm'')
              ,Value = [Amount]
         From  Sales 
     ) A
Pivot (sum(Value) For [Col] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns 96 columns from 00:00 to 23:45

The Code Generated

Select *
From (
        Select [SiteName]
              ,Col   = format(DateAdd(MINUTE,(DatePart(HOUR,[Time])*60) + ((DatePart(MINUTE,[Time]) / 15)*15),0),'HH:mm')
              ,Value = [Amount]
         From  Sales 
     ) A
Pivot (sum(Value) For [Col] in ([00:00],[00:15],[00:30],[00:45],[01:00],[01:15],[01:30],[01:45],[02:00],[02:15],[02:30],[02:45],[03:00],[03:15],[03:30],[03:45],[04:00],[04:15],[04:30],[04:45],[05:00],[05:15],[05:30],[05:45],[06:00],[06:15],[06:30],[06:45],[07:00],[07:15],[07:30],[07:45],[08:00],[08:15],[08:30],[08:45],[09:00],[09:15],[09:30],[09:45],[10:00],[10:15],[10:30],[10:45],[11:00],[11:15],[11:30],[11:45],[12:00],[12:15],[12:30],[12:45],[13:00],[13:15],[13:30],[13:45],[14:00],[14:15],[14:30],[14:45],[15:00],[15:15],[15:30],[15:45],[16:00],[16:15],[16:30],[16:45],[17:00],[17:15],[17:30],[17:45],[18:00],[18:15],[18:30],[18:45],[19:00],[19:15],[19:30],[19:45],[20:00],[20:15],[20:30],[20:45],[21:00],[21:15],[21:30],[21:45],[22:00],[22:15],[22:30],[22:45],[23:00],[23:15],[23:30],[23:45]) ) p

score 1 · Answer 2 · answered May 31 '17 at 02:40

This is an option that does NOT use dynamic SQL, and instead of 96 columns wide per row, generates one row per time slot. First, I am starting with a sample table of your data.

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' );

And the query which I will explain shortly

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 top 96
                        ROW_NUMBER() OVER(Order by AnyColumnInYourTable) Row
                    FROM     
                        AnyTableThatHasAtLeast96Rows ) 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

Now, the explanation...

First, the inner-most query alias result "timeSlots". This can query from ANY table that has at least the 96 time slot 15 minute increments you are looking for and does nothing but returns a result set numbered sequentially from 1 to 96.

Now that we have 96 rows, we get to the next outer query alias result "allTimes". This basically does date/time math and adds the 15 minute intervals * whatever "row" number value is an create all time slots into 96 rows. I have explicitly applied a start and end time to apply >= and <. But this query does nothing but creates the explicit time slots. And since I am casting the DATEADD() component to just the TIME, it does not matter what fixed "Date" value I start with -- in this case, 2017-01-01. All I care about are the time slots themselves. The results will be like...

TimeStart    TimeEnd
00:00:00     00:15:00
00:15:00     00:30:00
00:30:00     00:45:00
...
23:30:00     23:45:00
23:45:00     00:00:00   -- This one is special for the JOIN clause for time

Now, the LEFT JOIN... This is the SLIGHTLY tricky one

LEFT JOIN #Sales S
    on allTimes.TimeStart <= S.SaleTime
    AND S.SaleTime < allTimes.TimeEnd
    AND ( allTimes.TimeEnd < allTimes.TimeStart
        OR S.SaleTime <= allTimes.TimeEnd )

Here, left joining to the sales will always allow every time slot to be in the final result set. However, which slot does a given sale fit into? The Sale time must be GREATER OR EQUAL to the starting 15-minute interval...

AND..

Either... The endtime is less than the start (via the slot at 23:45 - 00:00 of the next morning) OR LESS then the beginning of the next time slot. Ex: 08:30 - 8:45 time slot is actually up to 8:44:xxxxx precision but always less than 8:45.

By doing this way with one row per time slot, I can get a count of transactions, sum of the transactions, you could even do avg, min, max for sales activity too, for finding trends.

score 1 · Answer 3 · answered May 31 '17 at 03:45

 WITH dates AS (
 SELECT CAST('2009-01-01' AS datetime) 'date'
 UNION ALL
 SELECT DATEADD(mi, 15, t.date) 
   FROM dates t
  WHERE DATEADD(mi, 15, t.date) < '2009-01-02')
 SELECT cast([date] as time) as [date] from dates

Use the above code to get 96 columns for the 15 min interval for a day.

Join the sales table with the above CTE.

Oto Shavadze · Answer 4 · 2017-05-31T12:01:20.860

Here recursive CTE generates 15 minute intervals for 24 hour (96 rows).

Then this result LEFT JOINed to subquery. In subquery Amount is grouped by 15 minute intervals for every hour.

In result, 00:00:00 corresponds sum of amounts, which happened from 00:00:00 to 00:14:59

00:15:00 = from 00:15:00 to 00:29:59

00:30:00 = from 00:30:00 to 00:44:59

00:45:00 = from 00:45:00 to 00:59:59

and so on for every 24 hour

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

insert into #Sales ( SiteName, SaleTime, Amount, BusinessDate )
values 
( 'A', '13:22:36', 888, '2017-01-02' ),
( 'A', '00:00:00', 20, '2017-01-02' ),
( 'A', '00:00:00', 30, '2017-01-02' ),
( 'A', '00:45:00', 88, '2017-01-02' ),
( 'A', '12:46:05', 22, '2017-01-02' ),
( 'A', '12:59:59', 22, '2017-01-02' ),
( 'A', '23:59:59', 10, '2017-01-02' );

-- Below is actual query:

with rec as(
    select cast('00:00:00' as time) as dt
    union all
    select DATEADD (mi , 15 , dt)  from rec 
    where
    dt < cast('23:45:00' as time)  
)
select rec.dt, t1.summ from rec
left join 
(select part, sum(Amount) as summ from (
    select *, case 
        when DATEPART ( mi , SaleTime ) < 15 then concat(SUBSTRING (cast(SaleTime as varchar) ,1 , 2 ), ':00:00')
        when DATEPART ( mi , SaleTime ) between 15 and 29 then concat(SUBSTRING (cast(SaleTime as varchar) ,1 , 2 ), ':15:00') 
        when DATEPART ( mi , SaleTime ) between 30 and 44 then concat(SUBSTRING (cast(SaleTime as varchar) ,1 , 2 ), ':30:00') 
        else concat(SUBSTRING (cast(SaleTime as varchar) ,1 , 2 ), ':45:00') 
        end as part
    from #Sales
    where BusinessDate = '2017-01-02'
) t
group by part) t1
on rec.dt = t1.part
order by rec.dt

rextester demo

Group sales data by time interval for every 15 min for one day

4 Answers4