30-Minute Aggregate Solution with T-SQL


create table orders (OrderDate datetime,OrderState int)
Insert into orders values (getdate(),1),(dateadd(minute,20,getdate()),1),(dateadd(minute,25,getdate()),1),(dateadd(minute,35,getdate()),1)

 declare @dt datetime = (Select Max(OrderDate) From Orders)
 ;WITH mycte AS
SELECT min(Dateadd(hour,DATEDIFF(hour,0,OrderDate),0)) OrderDate, OrderState
FROM orders
Group By OrderState

SELECT DATEADD(MINUTE,30,m.OrderDate) EndTime, m.OrderState
FROM mycte m
WHERE m.OrderDate< @dt

, mycte1 as
Select dateadd(minute
,Case When datepart(minute,OrderDate)<=15 Then 0
When datepart(minute,OrderDate)>15 ANd datepart(minute,OrderDate)<=30 Then 30
 When datepart(minute,OrderDate)>30 ANd datepart(minute,OrderDate)<=45 Then 30
 Else 60 End ,DATEADD(hour, DATEDIFF(hour,0,OrderDate),0) ) as dt30MinutesInterval,

from dbo.Orders )

Select m.OrderState, m.OrderDate,[Order Count] = COUNT(m1.dt30MinutesInterval)
 from mycte m Left join mycte1 m1 On m.OrderDate = m1.dt30MinutesInterval AND m.OrderState=m1.OrderState
 Group By m.OrderDate ,m.OrderState
 order by m.OrderState, m.OrderDate
drop table orders

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s