Years of memberships

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_range
from TX_CUST_MEMBERSHIP a
join T_CAMPAIGN b on a.campaign_no = b.campaign_no
join T_MEMB_LEVEL c on a.memb_level = c.memb_level

group by a.customer_no,c.description,a.memb_level

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

We had our system administrator create a script that we call TRX counter. It counts the number Member Transactions, for each household.