Ready to get started with anonymous questions today? It's easy! Learn more.
Become a Premium Member and unlock a new, free course in leading technologies each month.
USE VCUdm
--drop table ##temp_table_churn
begin try drop table ##temp_table_churn end try
begin catch end catch
DECLARE @year int
SET @year = (select year(end_date) from VCU.dbo.fy_dates)
select
Campus,
case
when month(JoinDate) in ('7','8','9','10','11','12') then year(JoinDate)+1
when month(JoinDate) in ('1','2','3','4','5','6') then year(JoinDate)
end as JoinYear,
--[AA Classification],
--case when month(JoinDate)=month(getdate()) then 1 else 0 end as New,
--case when month(ExpireDate)=month(getdate()) then 1 else 0 end as Expired,
--*
sum(case when month(JoinDate)=7 then 1 else 0 end) as JulyNew,
sum(case when month(JoinDate)=8 then 1 else 0 end) as AugNew,
sum(case when month(JoinDate)=9 then 1 else 0 end) as SepNew,
sum(case when month(JoinDate)=10 then 1 else 0 end) as OctNew,
sum(case when month(JoinDate)=11 then 1 else 0 end) as NovNew,
sum(case when month(JoinDate)=12 then 1 else 0 end) as DecNew,
sum(case when month(JoinDate)=1 then 1 else 0 end) as JanNew,
sum(case when month(JoinDate)=2 then 1 else 0 end) as FebNew,
sum(case when month(JoinDate)=3 then 1 else 0 end) as MarNew,
sum(case when month(JoinDate)=4 then 1 else 0 end) as AprNew,
sum(case when month(JoinDate)=5 then 1 else 0 end) as MayNew,
sum(case when month(JoinDate)=6 then 1 else 0 end) as JuneNew,
sum(case when Status='Renew' and month(JoinDate)=7 then 1 else 0 end) as JulyRenew,
sum(case when Status='Renew' and month(JoinDate)=8 then 1 else 0 end) as AugRenew,
sum(case when Status='Renew' and month(JoinDate)=9 then 1 else 0 end) as SepRenew,
sum(case when Status='Renew' and month(JoinDate)=10 then 1 else 0 end) as OctRenew,
sum(case when Status='Renew' and month(JoinDate)=11 then 1 else 0 end) as NovRenew,
sum(case when Status='Renew' and month(JoinDate)=12 then 1 else 0 end) as DecRenew,
sum(case when Status='Renew' and month(JoinDate)=1 then 1 else 0 end) as JanRenew,
sum(case when Status='Renew' and month(JoinDate)=2 then 1 else 0 end) as FebRenew,
sum(case when Status='Renew' and month(JoinDate)=3 then 1 else 0 end) as MarRenew,
sum(case when Status='Renew' and month(JoinDate)=4 then 1 else 0 end) as AprRenew,
sum(case when Status='Renew' and month(JoinDate)=5 then 1 else 0 end) as MayRenew,
sum(case when Status='Renew' and month(JoinDate)=6 then 1 else 0 end) as JuneRenew
into ##temp_table_churn
from membership_VIEW_alumni_campus_degree
group by
Campus,
case
when month(JoinDate) in ('7','8','9','10','11','12') then year(JoinDate)+1
when month(JoinDate) in ('1','2','3','4','5','6') then year(JoinDate)
end
--,[AA Classification]
select
year(getdate()) as Group1,
temp.Campus,
JoinYear as Year,
--temp.[AA Classification],
temp.JulyNew,
temp.JulyRenew,
seq.JulyExpire,
case when temp.JulyNew=0 then NULL else convert(varchar(5),100*(temp.JulyNew-seq.JulyExpire)/temp.JulyNew)+'%' end as JulyPer,
case when temp.JulyNew=0 then NULL else (temp.JulyNew-seq.JulyExpire) end as JulyNet,
temp.AugNew,
temp.AugRenew,
seq.AugExpire,
case when temp.AugNew=0 then NULL else convert(varchar(5),100*(temp.AugNew-seq.AugExpire)/temp.AugNew)+'%' end as AugPer,
case when temp.AugNew=0 then NULL else (temp.AugNew-seq.AugExpire) end as AugNet,
temp.SepNew,
temp.SepRenew,
seq.SepExpire,
case when temp.SepNew=0 then NULL else convert(varchar(5),100*(temp.SepNew-seq.SepExpire)/temp.SepNew)+'%' end as SepPer,
case when temp.SepNew=0 then NULL else (temp.SepNew-seq.SepExpire) end as SepNet,
temp.OctNew,
temp.OctRenew,
seq.OctExpire,
case when temp.OctNew=0 then NULL else convert(varchar(5),100*(temp.OctNew-seq.OctExpire)/temp.OctNew)+'%' end as OctPer,
case when temp.OctNew=0 then NULL else (temp.OctNew-seq.OctExpire) end as OctNet,
temp.NovNew,
temp.NovRenew,
seq.NovExpire,
case when temp.NovNew=0 then NULL else convert(varchar(5),100*(temp.NovNew-seq.NovExpire)/temp.NovNew)+'%' end as NovPer,
case when temp.NovNew=0 then NULL else (temp.NovNew-seq.NovExpire) end as NovNet,
temp.DecNew,
temp.DecRenew,
seq.DecExpire,
case when temp.DecNew=0 then NULL else convert(varchar(5),100*(temp.DecNew-seq.DecExpire)/temp.DecNew)+'%' end as DecPer,
case when temp.DecNew=0 then NULL else (temp.DecNew-seq.DecExpire) end as DecNet,
temp.JanNew,
temp.JanRenew,
seq.JanExpire,
case when temp.JanNew=0 then NULL else convert(varchar(5),100*(temp.JanNew-seq.JanExpire)/temp.JanNew)+'%' end as JanPer,
case when temp.JanNew=0 then NULL else (temp.JanNew-seq.JanExpire) end as JanNet,
temp.FebNew,
temp.FebRenew,
seq.FebExpire,
case when temp.FebNew=0 then NULL else convert(varchar(5),100*(temp.FebNew-seq.FebExpire)/temp.FebNew)+'%' end as FebPer,
case when temp.FebNew=0 then NULL else (temp.FebNew-seq.FebExpire) end as FebNet,
temp.MarNew,
temp.MarRenew,
seq.MarExpire,
case when temp.MarNew=0 then NULL else convert(varchar(5),100*(temp.MarNew-seq.MarExpire)/temp.MarNew)+'%' end as MarPer,
case when temp.MarNew=0 then NULL else (temp.MarNew-seq.MarExpire) end as MarNet,
temp.AprNew,
temp.AprRenew,
seq.AprExpire,
case when temp.AprNew=0 then NULL else convert(varchar(5),100*(temp.AprNew-seq.AprExpire)/temp.AprNew)+'%' end as AprPer,
case when temp.AprNew=0 then NULL else (temp.AprNew-seq.AprExpire) end as AprNet,
temp.MayNew,
temp.MayRenew,
seq.MayExpire,
case when temp.MayNew=0 then NULL else convert(varchar(5),100*(temp.MayNew-seq.MayExpire)/temp.MayNew)+'%' end as MayPer,
case when temp.MayNew=0 then NULL else (temp.MayNew-seq.MayExpire) end as MayNet,
temp.JuneNew,
temp.JuneRenew,
seq.JuneExpire,
case when temp.JuneNew=0 then NULL else convert(varchar(5),100*(temp.JuneNew-seq.JuneExpire)/temp.JuneNew)+'%' end as JunePer,
case when temp.JuneNew=0 then NULL else (temp.JuneNew-seq.JuneExpire) end as JuneNet
from ##temp_table_churn temp
join
(select
Campus,
case
when month(ExpireDate) in ('7','8','9','10','11','12') then year(ExpireDate)+1
when month(ExpireDate) in ('1','2','3','4','5','6') then year(ExpireDate)
end as ExpireYear,
--[AA Classification],
--case when month(ExpireDate)=month(getdate()) then 1 else 0 end as Expire,
--case when month(ExpireDate)=month(getdate()) then 1 else 0 end as Expired,
--*
sum(case when month(ExpireDate)=7 then 1 else 0 end) as JulyExpire,
sum(case when month(ExpireDate)=8 then 1 else 0 end) as AugExpire,
sum(case when month(ExpireDate)=9 then 1 else 0 end) as SepExpire,
sum(case when month(ExpireDate)=10 then 1 else 0 end) as OctExpire,
sum(case when month(ExpireDate)=11 then 1 else 0 end) as NovExpire,
sum(case when month(ExpireDate)=12 then 1 else 0 end) as DecExpire,
sum(case when month(ExpireDate)=1 then 1 else 0 end) as JanExpire,
sum(case when month(ExpireDate)=2 then 1 else 0 end) as FebExpire,
sum(case when month(ExpireDate)=3 then 1 else 0 end) as MarExpire,
sum(case when month(ExpireDate)=4 then 1 else 0 end) as AprExpire,
sum(case when month(ExpireDate)=5 then 1 else 0 end) as MayExpire,
sum(case when month(ExpireDate)=6 then 1 else 0 end) as JuneExpire
from membership_VIEW_alumni_campus_degree
group by
Campus,
case
when month(ExpireDate) in ('7','8','9','10','11','12') then year(ExpireDate)+1
when month(ExpireDate) in ('1','2','3','4','5','6') then year(ExpireDate)
end
--,[[AA Classification]
) seq on temp.JoinYear=seq.ExpireYear and temp.Campus=seq.Campus --and temp.[AA Classification]=seq.[AA Classification]
where temp.JoinYear >= @year-4 and temp.JoinYear <= @year
order by Year
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.