Sliding Count For Three Months with T-SQL Solutions (SQL Server 2008 and SQL Server 2012)




if (object_id('dbo.test','U') is not null  )
drop table test

Create table test (ID int, CustomerID int, OrderDate datetime, OrderItem int)
Insert into test  (ID, CustomerID, OrderDate, OrderItem) Values  (1,1,'1/1/2011',12)
,(2,1,'1/2/2011',22)
,(3,1,'3/20/2011',3)
,(4,1,'5/10/2011',4)
,(5,2,'1/1/2011',5)
,(6,2,'2/22/2011',2)
,(7,2,'4/12/2011',3)
,(8,2,'5/31/2011',2)
,(9,3,'4/1/2011',3)
,(10,3,'5/31/2011',4)
,(11,3,'6/22/2011',4)
,(12,3,'7/21/2011',6)

-- ---SQL Server 2012 or 2014

;With mycte as (select DATEADD(day, DATEDIFF(day,0,getdate()) - d.number,0)  dt, t.customerid  
from  master..spt_values d , (select distinct customerid from test) t WHERE d.type='P' 
AND d.number<2000)
,mycte1 as (Select m.CustomerID, m.dt, SUM(Case when OrderItem is not null Then 1 Else null End ) cnt 
from mycte m Left join test t on m.customerid=t.customerid AND m.dt=t.Orderdate 
group by m.CustomerID, m.dt)

 
 ,mycte2 as (
 Select CustomerID, dt, SUM(cnt) Over(Partition by CustomerID  Order by dt   Rows 90 Preceding) cnt90 
 from mycte1)
 Select distinct CustomerID from mycte2
WHERE cnt90>=3




--The expected results should be customer 1 and 3 since they ordered 3 items within 3 months. 
--We are still using SQL server 2008 R2
--Option 1
SELECT distinct o1.CustomerID from test o1 JOIN  test o2 
 ON o1.CustomerID=o2.CustomerID and (o2.OrderDate>dateadd(month,-3,o1.OrderDate) AND o2.OrderDate<=o1.OrderDate)
 Group by o1.CustomerID,o1.OrderDate,o1.OrderItem
 Having  (Count(o2.CustomerID)>=3) 
 Order by o1.CustomerID 


 --Option 2
 select  CustomerID 
from test t
 cross apply (select count(CustomerID) as cnt
    			from test
    			where  CustomerID=t.CustomerID AND (OrderDate <= t.OrderDate AND OrderDate>dateadd(month,-3,t.OrderDate))
    		) as rt
WHERE cnt>=3
Group By CustomerID
 


 
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