Ticket #940: 4.txt

--Total Cancels including term1,2,
select Memberid,termsbilled from member where CampaignName='BIZ001001001' and 
   JoinDate>='10/1/2003' and JoinDate<'11/1/2003' and CurrentStatus='Cancelled' order by memberid


--Total Cancels Term1
select count(Memberid) from member where CampaignName='BIZ001001001' and 
JoinDate>='10/1/2003' and JoinDate<'11/1/2003' and CurrentStatus='Cancelled' and (datediff("d",JoinDate,CancelDate)<=395  or datediff("d",JoinDate,CancelDate)is null)

--Total Cancels Term2
select count(Memberid) from member where CampaignName='BIZ001001001' and 
JoinDate>='10/1/2003' and JoinDate<'11/1/2003' and CurrentStatus='Cancelled' and (datediff("d",JoinDate,CancelDate)>395)

--Total Cancels Term3
select count(Memberid) from member where CampaignName='BIZ001001001' and 
JoinDate>='10/1/2003' and JoinDate<'11/1/2003' and CurrentStatus='Cancelled' and (datediff("d",JoinDate,CancelDate)>395)

--Trial Grace Cancels Term1
select Member.MemberID,Termsbilled,datediff("d",joindate,effcanceldate) from Member,Campaign where 
Member.CampaignName='BIZ001001001' and 
(member.JoinDate>='10/1/2003' and member.JoinDate<'11/1/2003') and 
Member.CampaignName=Campaign.CampaignName and 
datediff("d",Member.Joindate,Member.CancelDate)<=Campaign.TrialDays and Member.CurrentStatus='Cancelled' and Member.TermsBilled=0 order by Member.memberid


--Trial Grace Cancels Term2
select count(Member.MemberID) from Member,Campaign where 
Member.CampaignName='BIZ001001001' 
and (member.JoinDate>='10/1/2003' and member.JoinDate<'11/1/2003') 
and (datediff("d",dateadd("d",395,Member.Joindate),Member.CancelDate)<Campaign.GracePeriodDays)
and (datediff("d",dateadd("d",395,Member.Joindate),Member.CancelDate))>=1
and Member.CampaignName=Campaign.CampaignName
and Member.CurrentStatus='Cancelled' and Member.TermsBilled=1


--Term1 Method 1
select count(Member.MemberID) from Member, Campaign where 
Member.CampaignName='BIZ001001001' and 
(member.JoinDate>='10/1/2003' and member.JoinDate<'11/1/2003') and Member.CampaignName=Campaign.CampaignName and 
datediff("d",Member.Joindate,Member.CancelDate)>Campaign.TrialDays and 
(Member.CurrentStatusDetail='Method One' or Member.CurrentStatusDetail='Method One Activated') and 
(Member.TermsBilled=1 or Member.TermsBilled=0)



--Term1 Method 2
select Member.MemberID from Member, Campaign where 
Member.CampaignName='BIZ001001001' and 
(member.JoinDate>='10/1/2003' and member.JoinDate<'11/1/2003') and Member.CampaignName=Campaign.CampaignName and 
(datediff("d",dateadd("d",395,Member.Joindate),Member.CancelDate)<Campaign.GracePeriodDays) and
datediff("d",Member.Joindate,Member.CancelDate)>Campaign.TrialDays and 
(Member.CurrentStatusDetail='Method Two')  --order by Member.MemberID
and (Member.TermsBilled=1 or Member.TermsBilled=0) --order by Member.MemberID





--Term1 Method 2 Cancels
select Member.MemberID from Member, Campaign 
  where 
    Member.CampaignName='BIZ001001001' 
    and (member.JoinDate>='10/1/2003' and member.JoinDate<'11/1/2003') 
    and Member.CampaignName=Campaign.CampaignName 
    and (datediff("d",Member.Joindate,Member.CancelDate)>=Campaign.TrialDays) 
    and (datediff("d",Member.Joindate,Member.CancelDate)<=395)
    and Member.CurrentStatusDetail='Method Two' 
    and (Member.TermsBilled=1 or Member.TermsBilled=0)
    order by Member.MemberID


select count(Member.MemberID) from Member, Campaign 
  where 
    Member.CampaignName='BIZ001001001' 
    and (member.JoinDate>='10/1/2003' and member.JoinDate<'11/1/2003') 
    and Member.CampaignName=Campaign.CampaignName 
    and (datediff("d",Member.Joindate,Member.CancelDate)>=395+Campaign.GracePeriodDays) 
    and Member.CurrentStatusDetail='Method Two' 


    and (Member.TermsBilled=2 or Member.TermsBilled=0)
    order by Member.MemberID




select Member.MemberID from Member, Campaign 
  where 
    Member.CampaignName='BIZ001001001' 
    and (member.JoinDate>='10/1/2003' and member.JoinDate<'11/1/2003') 
    and Member.CampaignName=Campaign.CampaignName 
    and (datediff("d",Member.Joindate,Member.CancelDate)>=Campaign.TrialDays) 
    and (datediff("d",Member.Joindate,Member.CancelDate)<=402)
    and (Member.CurrentStatusDetail='Invalid Credit Cards')
    and (Member.TermsBilled=1 or Member.TermsBilled=0)
    order by Member.MemberID

select Member.MemberID from Member, Campaign 
  where 
    Member.CampaignName='BIZ001001001' 
    and (member.JoinDate>='10/1/2003' and member.JoinDate<'11/1/2003') 
    and Member.CampaignName=Campaign.CampaignName 
    and (Member.CurrentStatusDetail='Final Failure')
    and (Member.TermsBilled=0)
    order by Member.MemberID

select count(Member.MemberID) from Member, Campaign 
  where 
    Member.CampaignName='BIZ001001001' 
    and (member.JoinDate>='10/1/2003' and member.JoinDate<'11/1/2003') 
    and Member.CampaignName=Campaign.CampaignName 
    and (Member.CurrentStatusDetail='Final Failure' or Member.CurrentStatusDetail='Invalid Credit Cards')
    and (Member.TermsBilled=1)


-- or Member.TermsBilled=0

select count(Member.Memberid) from Member,Campaign where 
Member.CampaignName='BIZ001001001' and Member.CampaignName=Campaign.CampaignName and 
Member.JoinDate>='10/1/2003' and Member.JoinDate<'11/1/2003' and Member.CurrentStatus='Cancelled' and 
datediff("d",Member.JoinDate,Member.CancelDate)>(cast(Campaign.TrialDays as int)+Campaign.BillCycleDays)



select count(Memberid) from member where CampaignName='BIZ001001001' and 
JoinDate>='1/1/2005' and JoinDate<'2/1/2005' and CurrentStatus='Cancelled' and (datediff("d",JoinDate,CancelDate)>395)


select count(Memberid) from member where CampaignName='BIZ001001001' and 
JoinDate>='10/1/2003' and JoinDate<'11/1/2003' and 
CurrentStatus='Cancelled' and 
(datediff("d",JoinDate,CancelDate)<=395  or datediff("d",JoinDate,CancelDate)is null)



select count(Member.MemberID) from Member, Campaign where 
  Member.CampaignName='BIZ001001001' and 
 (member.JoinDate>='10/01/2003' and member.JoinDate<'11/1/2003') and 
 Member.CampaignName=Campaign.CampaignName and 
 datediff("d",Member.Joindate,Member.CancelDate)>=Campaign.TrialDays and 
 datediff("d",Member.Joindate,Member.CancelDate)<=(cast(Campaign.TrialDays as int)+Campaign.BillCycleDays) and 
 Member.CurrentStatusDetail='Method Two' and (Member.TermsBilled=1 or Member.TermsBilled=0)