I've been asked to compile a list of subscribers grouped into subsets of 5, 10, 20, and 30 years of consecutive seasons, with the catch of "consecutive" meaning no more than a 2 year gap between seasons. Can anyone take a crack at showing this intermediate (at best) SQL programmer how to do that using (I assume) some sort of WHILE loop logic?
What would be most helpful is even just a way to output, for each distinct(customer_no) in T_SUBSCRIPTION_HIST, the number of consecutive seasons as a separate row. I realize that such a table would have some customers show up more than once, but then I figure I could simply use a SELECT customer_no, MAX(num_cons_seasons) for each constituent, right?
Thank you, Tessiturians. I suspect I will get a lot of use out of this logic in future.
On this Day in History: The first commercial jet (the British De Haviland Comet) takes its first test flight.
This sounds similar to the Common Table Expression example Steve Carlock presented at the Tessitura Conference.
The presentation can be downloaded from the session details page.
Hope that helps
Mark
Here's a little ditty I wrote to get consecutive years giving. Maybe something like this would work using t_cust_subscription_summary?