Aggregating records within a set time period that resets from first occurrence

Database
Enthusiast

Aggregating records within a set time period that resets from first occurrence

Hello,

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.

4 REPLIES
Senior Apprentice

Re: Aggregating records within a set time period that resets from first occurrence

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?

Enthusiast

Re: Aggregating records within a set time period that resets from first occurrence

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.

Enthusiast

Re: Aggregating records within a set time period that resets from first occurrence

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.

Enthusiast

Re: Aggregating records within a set time period that resets from first occurrence

CREATE TABLE my_table
,NO FALLBACK
,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
VALUES (?,?,?,?);

select
 Customer
 , start_range
 , end_range
 , COUNT(Order_num) as Count_Orders
 , SUM(Sales) as sum_Sales
from
 (
 select
  aa.Customer
  ,bb.Order_num
  ,bb.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
 ) b
group by 1,2,3
order by 1,2 ;