Query to determine a count by rolling interval

Question

I'm looking at orders and trying to simulate some messageing that we are doing in the query so that Marketing can get a base line for how many responses they might expect if we changed the interval of our messageing. Messageing is triggered based on orders in this case. The idea is that a customer will reciev only one message regardless of how many orders placed untill the interval rolls over. for example

John orders on Monday,  message goes out.  (count = 1)
John orders on Thurday,
John orders on Sunday,
John orders on Tuesday  message goes out.  (count = 2)

To further complicate things there isnt a static set of intervals. The interval begins for the customer at the time of their first order. Thus Johns interval starts with Monday but Susans interval may start with Friday.

This is what I've got so far. Any help would be appreciated.

DECLARE @Interval INT = 7                     

SELECT o.intappid,
       o.intCustomerID, 
       MAX(o.intOrderID), 
       MAX(o.dtshipdate), 
       DATEPART(DAYOFYEAR, o.dtshipdate)/@Interval
  FROM .dbo.[order] o
 WHERE YEAR(o.dtShipDate) = 2012 
   AND o.intAppID = 18
   AND o.intCustomerID = 296
 GROUP BY o.intCustomerID, o.intAppID, DATEPART(DAYOFYEAR, o.dtshipdate)/@Interval
 order by o.intAppID, o.intcustomerid, DATEPART(DAYOFYEAR, o.dtshipdate)/@Interval

sample output from this query

intappid    intCustomerID   (No column name)    (No column name)    (No column name)
18          296             21954               2012-03-07          9
18          296             22671               2012-04-25          16
18          296             22728               2012-04-30          17
18          296             23354               2012-06-14          23

Answer 1

Try this:

1. establish the base date per customer. Something like SELECT MIN(dtshipdate) ... GROUP BY intCustomerId You can do this as a CTE or put the data in some table.
2. JOIN order to the above and calculate the number of intervals passed since first order
3. get the ROW_NUMBER partition by the result of the calculation in step 2, order by the date.
4. any row with ROW_NUMBER() = 1 needs to send a message.

This should work if the date of the first order sets the intervals. If you need to establish a new interval base date with each new order after a break longer than the interval, leave a comment below.