I have a business case where I need to take all customer orders over a 90 day period, and I want the output to show the number of orders placed within a 14 day period for each customer. The 14 day period would start at the first order for the customer and then reset again on day 15. So in the output it's possible to have more than 1 record for each customer depending on the number of orders and the dates. I have attached a .jpg file showing what the data would look like and how it would look summarized.
I have been really struggling with how to script this. I thought I could use some form of rows preceeding with MAX/MIN ordered analytic functions for dates, but the number rows to look back is not always the same. I've also looked at trying some self joins with ranks, etc. I don't know if I've just gotten to the point where I'm making it more difficult than it should be now but any help in the right direction would be great.
Why are there only two rows for customer 'D'?
When the 14 day period starts on Dec 1. then period #2 should be Dec 15. - 28. and #3 starts on Dec 29., so the last two rwos should be in seperate periods?
Sorry, I should have been more clear. The 14 day period "restarts" to the next order_dt. So in the case of customer D 1st period would start 12/1/15 and go through 12/14/15 as you indicated. The next period would start with the first order outside of that first 14 day period, so 12/23/15 and go through 1/5/16. That's why it only aggregates up to 2 rows.
That's where I'm struggling is how to maintain that 14 day boundary until it gets to the first record where order_dt > 1st order_dt + 13 and then update the range/period for each successive group within a customer group.
After some more trial and error I was able to work this out, and of course it was simpler than I was thinking it would be. I was able to accomplish my business case by using a self-join. I've posted the code to recreate the table and outcome in the subsequent post. Thanks for anyone who looked.
CREATE TABLE my_table
,NO BEFORE JOURNAL
,NO AFTER JOURNAL
Customer CHAR(1) TITLE 'Customer' NOT NULL
,Order_num INTEGER TITLE 'Order Number' NOT NULL
,Order_Dt DATE TITLE 'Order Date'
,Sales DECIMAL(10,2) TITLE 'Sales'
UNIQUE PRIMARY INDEX( Customer,Order_num );
INSERT INTO my_table
, COUNT(Order_num) as Count_Orders
, SUM(Sales) as sum_Sales
, min(aa.Order_Dt) as start_range
, min(aa.Order_Dt)+13 as end_range
from my_table aa
inner join my_table bb
on aa.Customer = bb.Customer
and bb.Order_Dt between aa.Order_Dt and aa.order_dt + 13
group by 1,2,3
group by 1,2,3
order by 1,2 ;