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