Does anyone know how to pull the number of years the members in a membership level have had membership (including memberships given as a benefit of sponsorship and non-consecutive years of membership)?
Our organization would like to print a listing of members of our highest level of membership and group them by the number of years they have had a membership at that level. In the past, this was done manually and took several days to complete. Has anyone attempted this successfully in Tess?
Hello Phyllis, I just slapped this together to give you an idea. It may work for you or might want to change the logic a bit. Run it SSMS and take a look. Again, just an example, but I hope it helps you.
Travis
select a.customer_no, c.description as memb_desc,a.memb_level, COUNT(distinct b.fyear)num_fyears,case when min(b.fyear)=MAX(b.fyear) then convert(varchar,min(b.fyear)) when min(b.fyear)<>MAX(b.fyear) then convert(varchar,min(b.fyear))+' - '+convert(varchar,MAX(b.fyear)) end as fyear_rangefrom TX_CUST_MEMBERSHIP a join T_CAMPAIGN b on a.campaign_no = b.campaign_nojoin T_MEMB_LEVEL c on a.memb_level = c.memb_levelgroup by a.customer_no,c.description,a.memb_levelorder by a.customer_no
Example output:
Our conversion data (Provenue to Tessitura) may be incomplete. So, the sample output may not reflect the range 100%. You may also want the dates listed out in case of gaps in the range (ie. 1981,1999, 2001). If you want help let me know.