Finding drop off rate from membership table in SQL Server 2005

Question

We have a view that stores the history of membership plans held by our members and we have been running a half price direct debit offer for some time. We've been asked to report on whether people are allowing the direct debit to renew (at full price) but I'm no SQL expert!

The view in effect is

memberRef, historyRef, validFrom, validTo,MembershipType,PaymentType,totalAmount

Here

• memberRef identifies the person (int)
• historyRef identifies this row (int)
• validFrom and validTo are the start and end of the plan (datetime)
• MembershipType is the type of plan (int)
• PaymentType is direct debit or credit card (a string - DD or EFT)
• totalAmount is the price of the plan (decimal)

I'm wondering if there is a query as opposed to a cursor I can use to count the number of policies which are at half price and have another direct debit policy that follows on from it.

If we can also capture if that person first joined at half price or if there was a gap where membership had lapsed before they took the half price incentive that would be great.

Thanks in advance for any help!

For example

select count(MemberRef), max(vhOuter.validFrom) "most recent plan start",
(select top(1) vh2.validFrom 
from v_Membershiphistory vh2 
where (vh2.totalamount = 14.97 or vh2.totalamount = 25.50)  
and vh2.memberref = vhOuter.memberref
order by createdat desc
) "half price plan start"
from v_membershiphistory vhOuter
where vhOuter.memberref in (select vh1.memberref from v_membershiphistory vh1 where     vh1.totalamount = 14.97 or vh1.totalamount = 25.50)--have taken up offer
group by memberref
having max(vhOuter.validFrom) > (select top(1) vh2.validFrom 
                                from v_Membershiphistory vh2 
                                where (vh2.totalamount = 14.97 or vh2.totalamount = 25.50)  
                                and vh2.memberref = vhOuter.memberref
                                order by createdat desc
                            )

This will display the members who have a half price plan and have a valid from date that is greater than the valid from date of that plan.

Not quite right as we should be testing that it is the same plan but...

if I change the select here to just count(memberRef) I get the count of memberRef for the member I'm grouping for each member I'm grouping i.e. for 5220 results I'd get 5220 rows returned each with in effect the number of plans I've selected

But I need to count the number of people taking the offer and proportion that renew. Also that renewal rate in the population that aren't taking the offer (which I'm guessing is a trivial change once I've got one set sorted)

I suppose I'm looking at how one operates on the set but compares multiple rows for each distinct person without using a cursor. But I might be wrong :)

Answer 1

try something like:

SELECT
    a.*, b.*
    FROM YourTable           a
        INNER JOIN YourTable b On a.memberRef=b.memberRef and a.validToDate<b.validFromDate
    WHERE b.PaymentType='?direct debit?' and a.Cost='?half price?'

to get just counts use something like:

SELECT
    COUNT(a.memberRef) AS TotalCount
    FROM YourTable           a
        INNER JOIN YourTable b On a.memberRef=b.memberRef and a.validToDate<b.validFromDate
    WHERE b.PaymentType='?direct debit?' and a.Cost='?half price?'