Sliding Count For Three Months with T-SQL

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)

-- ---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
 --Option 3  
;with mycte as
        ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate) as rn
    from test

select distinct   m.CustomerID
from mycte as m
inner join mycte as n
    on n.CustomerID = m.CustomerID and n.rn = m.rn + 2
where    DATEDIFF(DAY, m.OrderDate, n.OrderDate) <= 90



Leave a Reply

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

You are commenting using your 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