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)
,(getdate(),2),(dateadd(minute,20,getdate()),2),(dateadd(minute,170,getdate()),2),(dateadd(minute,40,getdate()),2),(dateadd(minute,45,getdate()),2)

 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

UNION ALL
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,

OrderState
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
Advertisements


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s