Search code examples
sqlsql-servert-sqlsql-server-2012

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

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

Solution

  • 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

    enter image description here

    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