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.
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!