Group data by interval of 15 minutes and use cross

Question

I am trying to write a query based on datetime and weekday in sql server where my output should be like :

My table descriptions are:

   **Branch**(DateKey integer,
   BranchName varchar2(20),
   TransactionDate datetime,
   OrderCount integer)

   **Date**(DateKey integer PrimaryKey,
   DayNameofWeek varchar2(15))

This is the raw data I have

Answer 1

So, this is quite a long shot but I solved it the following way:

I created a table valued function, which would take a date as a parameter and find all 15-minute intervals during that day. For each day it would go from 00:00, to 00:15, 00:30 up to 23:30, 23:45, and 23:59. It also returns each interval start time and end time, since we will need to use this for every row in your branch table to check if they fall into that time slot and if so, count it in.

This is the function:

create function dbo.getDate15MinIntervals(@date date)
returns @intervals table (
   [interval] int not null,
   [dayname] varchar(20) not null,
   interval_start_time datetime not null,
   interval_end_time datetime not null
) 
as
begin 

    declare @starttime time = '00:00';
    declare @endtime time = '23:59';

    declare @date_start datetime;
    declare @date_end datetime; 
    declare @min datetime; 

    select @date_start = cast(@date as datetime) + cast(@starttime as datetime), @date_end = cast(@date as datetime) + cast(@endtime as datetime);

    declare  @minutes table ([date] datetime)

    insert into @minutes values (@date_start), (@date_end) -- begin, end of the day

    select @min = DATEADD(mi, 0, @date_start)

    while @min < @date_end
    begin
        select @min = DATEADD(mi, 1, @min)
        insert into @minutes values (@min)
    end 

    insert into @intervals
    select ([row]-1)/15+1 intervalId, [dayname], min(interval_time) interval_start_time

   > -- **NOTE: This line is the only thing you need to change:**

   , DATEADD(ms, 59998, max(interval_time)) interval_end_time


    from 
    (
        select row_number() over(order by [date]) as [row], [date],  datename(weekday, [date]) [dayname], [date] interval_time
        from @minutes
    ) t 
    group by ([row]-1)/15+1, [dayname]
    order by ([row]-1)/15+1

    return 

end 

 --example of calling it:
 select * from dbo.getDate15MinIntervals('2017-07-14')

Then, I am querying your branch table (you don't really need the Date table, the weekday now you have it in the function but even if not, there's a DATENAME function in SQL Server, starting with 2008 that you can use.

I would query your table like this:

select branchname, [dayname], ISNULL([11:30], 0) as [11:30], ISNULL([11:45], 0) as [11:45], ISNULL([12:00], 0) as [12:00], ISNULL([12:45], 0) as [12:45]
from 
( 
    select intervals.[dayname]
         , b.branchname
         , convert(varchar(5), intervals.interval_start_time, 108) interval_start_time -- for hh:mm format
         , sum(b.ordercount) ordercount
    from branch b cross apply dbo.getDate15MinIntervals(CAST(b.TransactionDate as date)) as intervals
    where b.transactiondate between interval_start_time and interval_end_time
    group by intervals.[dayname], b.branchname, intervals.interval_start_time, intervals.interval_end_time
) t 
pivot ( sum(ordercount) for interval_start_time in ( [11:30], [11:45] , [12:00], [12:45] )) as p

Please note I have used in the PIVOT function only the intervals I can see in the image you posted, but of course you could write all 15-minute intervals of the day manually - you would just need to write them once in the pivot and once in the select statement - or optionally, generate this statement dynamically.