Query to determine a count by rolling interval
Try this:


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.
JOIN order to the above and calculate the number of intervals passed since first order
get the ROW_NUMBER partition by the result of the calculation in step 2, order by the date.
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.