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?
Hi Matt
Are you after something like below? I can send you the script. This report shows Susbcriber count for subsequent 5,10,15 years.
cheers, Varsha
All of the responses were helpful to an extent. But I am curious, do any of your solutions take into account gaps between years, or simply subtract the min(fyear) from the max(fyear)? I need to account for gaps in that my Development colleagues consider "consecutive" years to be no more than 3 years apart from each other.
Thanks again for the responses!