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?!

Answer 1

I used mster.l.spt_values(just have number) to get all time, in my sample Business Date and time is in 1 field as time. enter image description here

 DECLARE @mindate datetime= (select cast(min(time)as date) FROM [SQL_Quiz].[dbo].[Sales] )
 DECLARE @maxdate datetime= (select   dateadd(minute,15,max(time) )  FROM [SQL_Quiz].[dbo].[Sales] );
 with cteTime1 as(
                    SELECT  DATEADD(MINUTE, 15 * (number),@mindate) as starttime ,DATEADD(MINUTE, 15 * (number+1),@mindate) as Endtime
                    FROM    master..spt_values
                    where type = 'P'  AND  number<DATEDIFF(hour,  @mindate,@maxdate)*4
                    )

SELECT TOP (1000) [SiteName],  startTime, EndTime    ,sum(isnull([Amount],0)) As SumAmount
FROM  [cteTime1] left join  [dbo].[Sales]
on time>starttime and time< Endtime
group by [SiteName],starttime,EndTime 
order by 2

Answer 2

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

Answer 3

 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.

Answer 4

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.

Answer 5

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