Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
It is not quite clear what you are looking for. A select can't send messages. Can you post same example data and expected results? –  Sebastian Meine Mar 12 '13 at 12:37
    
yes the select is simulating when the messages would have gone out. Each count of 1 would be a message that would have been sent. So in the sample data abover I would want to count the first and third row because they are each more than 7 days appart. –  Lumpy Mar 12 '13 at 13:21

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
That'll do it. Thanks Sebastian. –  Lumpy Mar 12 '13 at 16:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.