mssql存储过程中同比问题

with tmp as

(

	select 21 as num1,201503 as ym, 1 as sort

	union all

	select 3 as num1,201509 as ym, 1 as sort

	union all

	select 6 as num1,201510 as ym, 1 as sort

	union all

	select 7 as num1,201403 as ym, 2 as sort

	union all

	select 21 as num1,201405 as ym, 2 as sort

	union all

	select 2 as num1,201409 as ym, 2 as sort

	union all

	select 4 as num1,201410 as ym, 2 as sort

)

select t1.ym,t1.num1,t2.num1, cast((t1.num1 - t2.num1) * 1.0 / t2.num1 as decimal(18,2)) as rate

	from tmp t1

	left join tmp t2 on t1.ym = t2.ym + 100

create table dbo.t1

(

	num1 decimal(18,2),

	ym int,

	sort tinyint

);



insert into dbo.t1

(

	num1,

	ym,

	sort

)

select	21,201503,1 union all

select	3,201509,1 union all

select	6,201510,1 union all

select	7,201403,2 union all

select	21,201405,2 union all

select	2,201409,2 union all

select	4,201410,2;



select	*

from	dbo.t1 with(nolock);



DECLARE @Year INT=2015;



--解析年月

SELECT	CAST(REPLACE(CONVERT(VARCHAR(7),CAST(LTRIM(@Year)+'-'+LTRIM(number)+'-01' AS DATE),120),'-','') AS INT) AS ThisYear,

		CAST(REPLACE(CONVERT(VARCHAR(7),CAST(LTRIM(@Year-1)+'-'+LTRIM(number)+'-01' AS DATE),120),'-','') AS INT) AS Lastyear

INTO	dbo.#T1

FROM	master..spt_values 

WHERE	[type]='p'

AND		number>0

AND		number<13;



;WITH AAA AS

(

	SELECT	A.ThisYear,

			A.Lastyear,

			ISNULL(B.num1,0) AS num1,

			ISNULL(C.num1,0) AS num2

	FROM	dbo.#T1 AS A LEFT JOIN

			dbo.T1 AS B ON A.ThisYear=B.ym LEFT JOIN

			dbo.T1 AS C ON A.LastYear=C.ym

)

SELECT	*,

		CASE WHEN num1=0 THEN 0 ELSE CAST((num1-num2)/num1*100 AS DECIMAL(18,2)) END AS Rate

FROM	AAA

ORDER BY ThisYear;



TRUNCATE TABLE dbo.#T1;



DROP TABLE dbo.#T1;

执行结果：

ThisYear Lastyear	num1	num2	Rate

201501	201401	0.00	0.00	0.00

201502	201402	0.00	0.00	0.00

201503	201403	21.00	7.00	66.67

201504	201404	0.00	0.00	0.00

201505	201405	0.00	21.00	0.00

201506	201406	0.00	0.00	0.00

201507	201407	0.00	0.00	0.00

201508	201408	0.00	0.00	0.00

201509	201409	3.00	2.00	33.33

201510	201410	6.00	4.00	33.33

201511	201411	0.00	0.00	0.00

201512	201412	0.00	0.00	0.00

存储过程就不写了，自己试着把代码COPY进去即可。[size=13]

[/size]

给你一个我实际写的月环比的例子参考下看看有用没有

declare @StartDate datetime

declare @EndDate datetime

declare @Date datetime

declare @OrganizationId uniqueidentifier

declare @PointId uniqueidentifier

declare @BusinessUnitId uniqueidentifier



set @StartDate = '2014-09-29'

set @EndDate ='2014-10-07'

set @Date ='2014-10-1'

set @OrganizationId ='82742315-a603-4525-a597-5fc8df60c6c4'

set @PointId ='f399b668-7fc3-49fb-b444-a31001433865'

set @BusinessUnitId ='00ddcd96-adb7-4956-bdea-d7133b0e078e'



select	DATEADD(mm, DATEDIFF(mm,0,@Date),0) as fistday

		,DATEADD(mm, DATEDIFF(mm,0,@Date)-1,0) as lastfistday

		,DATEADD(mm,-1,@Date) as lastday

into #TDay 



select  TGSBU.ParentId

		,TGSBU.BusinessUnitId

		,TGSBU.BusinessUnitNo

		,TGSBU.Name

		,LastSaleMoney

		,SaleMoney

		,(SaleMoney-LastSaleMoney) as addMoney

		,(case when LastSaleMoney = 0  then 0 else (SaleMoney-LastSaleMoney)/LastSaleMoney end) as addmoneyrate

		,LastBillnum,Billnum,(Billnum-LastBillnum) as addnum

		,(case when LastBillnum = 0  then 0 else (Billnum-LastBillnum)*1.0/LastBillnum end) as addnumrate

		,(case when LastSaleMoney = 0 or LastBillnum =0 then 0 else LastSaleMoney/LastBillnum end) as LastAvgSaleMoney

		,(case when SaleMoney = 0 or Billnum =0 then 0 else SaleMoney/Billnum end) as AvgSaleMoney

from	dbo.F_GetSubBusinessUnit(@OrganizationId,@BusinessUnitId,10) TGSBU

		left join 

		(

				select  dbo.AccontBill.BusinessUnitId

						,isnull(SUM(case when AccoDate between lastfistday and lastday then SaleMoney else 0 end),0) as LastSaleMoney

						,isnull(SUM(case when AccoDate between fistday and @Date then SaleMoney else 0 end),0) as SaleMoney

						,isnull(SUM(case when AccoDate between lastfistday and lastday then 1 else 0 end),0) as LastBillnum

						,isnull(SUM(case when AccoDate between fistday and @Date then 1 else 0 end),0) as Billnum

				from	dbo.AccontBill,#Tday

				where	dbo.AccontBill.OrganizationId=@OrganizationId

						and ((AccoDate BETWEEN lastfistday AND @Date)) 

						and (StateCode>=0) 

				group by dbo.AccontBill.BusinessUnitId

			) AS TAB ON ( TGSBU.BusinessUnitId = TAB.BusinessUnitId)

where	TGSBU.ParentId <> '00000000-0000-0000-0000-000000000000'

order by TGSBU.BusinessUnitNo

drop table #TDay