背景:根据数据表里的延期缴费开始时间及月数,将费用分摊在每个月上
--年份,月份,月数
select * ,from Park_monthcard p where CreateTime >='2018-05-24 00:00:00' and PayStatus=1 --AND carNo LIKE '%3F314%' OR carNo LIKE '%D59D%'
由于case when 只能嵌套10层,故改进后:
--改进 case只能嵌套到10层
--年份,月份,月数
select * ,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=1 then p.money/p.month
else 0
end as mon0,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=2 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=1 then p.money/p.month
else 0
end as mon1,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=3 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=2 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=1 then p.money/p.month
else 0
end as mon2,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=4 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=3 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=2 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=1 then p.money/p.month
else 0 end as mon3,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=5 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=4 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=3 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=2 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=1 then p.money/p.month
else 0 end as mon4,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=6 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=5 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=4 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=3 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=2 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=1 then p.money/p.month
else 0 end as mon5,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=7 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=6 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=5 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=4 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=3 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=2 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=1 then p.money/p.month
else 0 end as mon6,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=8 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=7 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=6 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=5 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=4 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=3 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=2 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+7,GETDATE())) and p.month>=1 then p.money/p.month
else 0 end as mon7,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=9 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=8 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=7 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=6 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=5 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=4 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=3 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+7,GETDATE())) and p.month>=2 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+8,GETDATE())) and p.month>=1 then p.money/p.month
else 0 end as mon8,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=10 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=9 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=8 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=7 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=6 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=5 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=4 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+7,GETDATE())) and p.month>=3 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+8,GETDATE())) and p.month>=2 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+9,GETDATE())) and p.month>=1 then p.money/p.month
else 0 end as mon9,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=11 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=10 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=9 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=8 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=7 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=6 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=5 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+7,GETDATE())) and p.month>=4 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+8,GETDATE())) and p.month>=3 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+9,GETDATE())) and p.month>=2 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+10,GETDATE())) and p.month>=1 then p.money/p.month
else 0 end as mon10,
case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=12 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=11 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=10 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=9 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=8 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=7 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=6 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+7,GETDATE())) and p.month>=5 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+8,GETDATE())) and p.month>=4 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+9,GETDATE())) and p.month>=3 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+10,GETDATE())) and p.month>=2 then p.money/p.month
when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+11,GETDATE())) and p.month>=1 then p.money/p.month
else 0 end as mon11
from Park_monthcard p where CreateTime >='2018-05-24 00:00:00' and PayStatus=1
select S.RealName,S.PropertyName,case when S.BuildingName is not null then S.BuildingName else '暂无' end as BuildingName,case when S.RoomNo is not null then S.RoomNo else '暂无' end as RoomNo,S.parkUserID,S.carNo, sum(S.mon0) as mon0,sum(S.mon1) as mon1,sum(S.mon2) as mon2,sum(S.mon3) as mon3,sum(S.mon4) as mon4,sum(S.mon5) as mon5,sum(S.mon6) as mon6 ,sum(S.mon7) as mon7,sum(S.mon8) as mon8,sum(S.mon9) as mon9,sum(S.mon10) as mon10,sum(S.mon11) as mon11,sum(S.mon12) as mon12 ,sum(S.monf1) as monf1,sum(S.monf2) as monf2,sum(S.monf3) as monf3,sum(S.monf4) as monf4,sum(S.monf5) as monf5,sum(S.monf6) as monf6 ,sum(S.monf7) as monf7,sum(S.monf8) as monf8,sum(S.monf9) as monf9,sum(S.monf10) as monf10,sum(S.monf11) as monf11,sum(S.monf12) as monf12 from ( select p.carNo as carNo,p.parkUserID as parkUserID,tu.LoginName as LoginName,tu.RealName as RealName,tp.PropertyId as PropertyId,tp.PropertyName as PropertyName,tb.BuildingId as BuildingId,tb.BuildingName as BuildingName,tr.RoomId as RoomId ,tr.RoomNo as RoomNo ,tr.Apply as Apply, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as monf12, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=2 then p.money/p.month else 0 end as monf11, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=3 then p.money/p.month else 0 end as monf10, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=4 then p.money/p.month else 0 end as monf9, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=5 then p.money/p.month else 0 end as monf8, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=6 then p.money/p.month else 0 end as monf7, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=7 then p.money/p.month else 0 end as monf6, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=8 then p.money/p.month else 0 end as monf5, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=9 then p.money/p.month else 0 end as monf4, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=10 then p.money/p.month else 0 end as monf3, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=11 then p.money/p.month else 0 end as monf2, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-12,GETDATE())) and p.month>=12 then p.money/p.month else 0 end as monf1, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=1 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-11,GETDATE())) and p.month>=12 then p.money/p.month else 0 end as mon0, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-10,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon1, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-9,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon2, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-8,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon3, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-7,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon4, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-6,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon5, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-5,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon6, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-4,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+7,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon7, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-3,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+7,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+8,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon8, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-2,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+7,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+8,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+9,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon9, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,-1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,-1,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+7,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+8,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+9,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+10,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon10, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+7,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+8,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+9,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+10,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+11,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon11, case when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+0,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+0,GETDATE())) and p.month>=13 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+1,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+1,GETDATE())) and p.month>=12 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+2,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+2,GETDATE())) and p.month>=11 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+3,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+3,GETDATE())) and p.month>=10 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+4,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+4,GETDATE())) and p.month>=9 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+5,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+5,GETDATE())) and p.month>=8 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+6,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+6,GETDATE())) and p.month>=7 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+7,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+7,GETDATE())) and p.month>=6 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+8,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+8,GETDATE())) and p.month>=5 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+9,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+9,GETDATE())) and p.month>=4 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+10,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+10,GETDATE())) and p.month>=3 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+11,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+11,GETDATE())) and p.month>=2 then p.money/p.month when Convert(varchar(4),newBeginDate,120)=YEAR( DATEADD(MONTH,+12,GETDATE())) and datepart(mm,newBeginDate)= MONTH(DATEADD(MONTH,+12,GETDATE())) and p.month>=1 then p.money/p.month else 0 end as mon12 from Park_monthcard p left join t_User tu on p.parkUserID=tu.UserId left join t_Property tp on tp.PropertyId=tu.PropertyId left join t_Building tb on tb.BuildingId=tu.BuildingId left join t_Room tr on tr.RoomId=tu.RoomId where p.CreateTime >='2018-05-24 14:00:00' and p.PayStatus=1 -- AND (carNo LIKE '%008ES%' OR carNo LIKE '%1C86W%' OR carNo LIKE '%7S719%') ) S group by S.carNo,S.parkUserID,S.parkUserID,S.LoginName,S.RealName,S.PropertyId ,S.PropertyName, S.BuildingId ,S.BuildingName ,S.RoomId ,S.RoomNo ,S.Apply