Find count of orders in 30 day sliding period? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Find count of orders in 30 day sliding period?

Author

Topic

JohnPaulSt
Starting Member

2 Posts

Posted - 2006-05-24 : 12:15:21

Business rule: Find the customers who have ever had more than 10 orders in any 30 day period.

-30 day period, not calendar MONTH().

-Not in the last 30 days, but in any 30 day period in the past.

Table: CustomerOrders
Fields: Order_Date, CustomerID

SQL Server 2000. I can think of some iteration techniques, where by I could select orders in to a TEMP table and ORDER BY CustomerID, then Order_Date. Then, iterate thru that result, keeping up with the count of orders, over a 'sliding' period. Number of days since last order < 30 days, order count > 10, etc.??

But is there a JOIN / AGGREGATE I could employ though?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-24 : 16:10:52

See if this works for you:

SELECT A.CustomerID, A.Order_Date, COUNT(*)
FROM CustomerOrders A CROSS JOIN CustomerOrders B
WHERE A.CustomerID = B.CustomerID
AND B.Order_Date BETWEEN A.Order_Date AND A.Order_Date +29
AND B.Order_Date <= getdate() -29
GROUP BY A.CustomerID, A.Order_Date
HAVING COUNT(*) > 10
ORDER BY A.CustomerID, A.Order_Date

The "AND B.Order_Date <= getdate() -29" part is there so that it doesn't count the periods within the last 29 days multiple times. I didn't test this out with actual data, so you may have to tweak it a bit.

JohnPaulSt
Starting Member

2 Posts

Posted - 2006-05-24 : 18:20:14

That is excellent, thanks!

Now all I need to do is 'squash' the customers, in order to get a listing of customers who satisfy question: Who has had more than 10 order in any 30 day period?

CustA 2005-11-02 00:00:00 33
CustA 2005-11-14 00:00:00 20
B Per 2005-06-03 00:00:00 15
B Per 2005-06-08 00:00:00 196
XYZ 2005-02-05 00:00:00 18
XYZ 2005-04-02 00:00:00 12
XYZ 2005-06-10 00:00:00 19

I'll make the field list: DISTINCT A.CustomerID and I'm there - outstanding solution, thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources