Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT
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.