Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can anyone tell me how to do the following...Show IDs of customers having > 1 purchase within any 5 day period
I have a table that contains these fields: CUST_ID, UNIQUE_PURCHASE_ID, SALE_DATE
It's not quite clear for which time period you want to evaluate, i.e. any 5 subsequent days in a period starting from when and ending when. I guess all of your customers qualify for your condition before they were born.
Basically, order your list of sales by customer and sale_date and calculate the difference in days between two subsequent purchases (of the same customer, and maybe taking into account the start and end dates of the total time period you want to evaluate your condition for).
If the number of days between two subsequent purchases is larger than 5, than this customer does not fulfill your requirement (there are 5 day periods possible where she doesn't purchased anything).
Create a flag for this customer, which you can use in your charts to filter your customers.
To order your list of customers, use a resident table load with ORDER BY clause.
To access the previous record (to check for same customer and previous purchase date), you can use inter record functions like Previous() or Peek().
Please do not double post!
The initial thread is here:
How do I Show IDs of customers having > 1 purchase within any 5 day period.
This thread seems to be even older than the other, maybe it has just been awaiting approval by moderation?
But in general, I agree with Frank, please try to avoid posting the same request multiple times, it clutters the forum and just makes it hard to follow a discussion.
Instead of posting multiple identical threads, add some more information to your original, single request, e.g. how your data and current model looks like, and what exactely you are expecting as a result (as I tried to mention above and when looking to the other thread, it's not clear what you expect as a result).
Sorry. Thanks Frank and Stefan.