I am writing a procedure to pull members who have not renewed. I want to include any membership that is not active or pending that falls within the expiration date range provided. My problem is that I am getting memberships even if the membership was renewed and there is a current active membership. So the code is looking at each individual membership and pulling any even if there is a more recent active membership. How do I tell it to exclude the membership if there is another active membership?
Help appreciated!
Gloria
Hi Gloria,
I think a lot of people try to finish the whole thing in one step.
there are a lot of examples in imperisaro.
case this sum that group by and union all.
if you use the logic in this kind way, it will make life a lot harder.
just try to think in another way.
there is always another way.
if you cannot finish a t-sql in one step, try it in two steps or three steps.
let's say you create a first list with all memebership in it #tmp1.
then second step pick up only expired memebership #tmp2.
then the third list only pick up active memebership #tmp3.
so
select * from #tmp2 where #tmp2.customer_no not in (select #tmp3.customer_no from #tmp3)
this will give you what you want to have.