朋友们,帮帮忙!!!

[renadg]

name ym money pp

1 200801 88.1
1 200802 189
1 200803 991
1 200901 88.1
2 200801 50
2 200802 900
2 200902 1800


以上这个表,
1.我想算出增长率,就是200802的money减去200801的money再除以200801的money得出来的数,
依此类推,如(200803-200802)/200802...

比如说: (189-88.1)/88.1

2. 还有就是算出第二年同样的月的增长率,就是200901的money-200801的money再除以200801的money

这个要怎么算啊..我想的头都有点晕了!!!!还是我根本就有点笨....555555
请大家帮帮忙.谢谢了!!!
比较急!

CREATE TABLE [pierc] (
	[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ym] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[money] [numeric](18, 2) NULL ,
	[pp] [numeric](18, 2) NULL 
) ON [PRIMARY]
GO



insert pierc
select '1','200801','88.1','' union
select '1','200802','189','' union
select '1','200803','991','' union
select '1','200901','88.1','' union
select '2','200801','50','' union
select '2','200802','900','' union
select '2','200902','1800',''

[renadg]

要name相同的进行比较算出增长率

[renadg]

也就是算同样年度的月增长率...
不同年度相同月的增长率

[feixianxxx]

select '1','200803','991','' union
select '1','200901','88.1','' union

这2条要算不算

[liangCK]

CREATE TABLE [pierc] (
    [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [ym] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [money] [numeric](18, 2) NULL ,
    [pp] varchar(10) NULL 
) ON [PRIMARY]
GO



insert pierc
select '1','200801','88.1','' union all
select '1','200802','189','' union all
select '1','200803','991','' union all
select '1','200901','88.1','' union all
select '2','200801','50','' union all
select '2','200802','900','' union all
select '2','200902','1800',''


SELECT A.name,A.ym,A.money,
    CAST(ISNULL((A.money-ISNULL(B.money,0))*100/NULLIF(B.money,0),0) AS numeric(18,2)) AS 月增长率,
    CAST(ISNULL((A.money-ISNULL(C.money,0))*100/NULLIF(C.money,0),0) AS numeric(18,2)) AS 年增长率
FROM pierc AS A
    LEFT JOIN pierc AS B
ON YEAR(CAST(A.ym+'01' AS datetime))=YEAR(CAST(B.ym+'01' AS datetime))+MONTH(CAST(B.ym+'01' AS datetime))/12
    AND A.name=B.name 
    AND MONTH(CAST(A.ym+'01' AS datetime))=MONTH(CAST(B.ym+'01' AS datetime))%12+1
    LEFT JOIN pierc AS C
ON A.name = C.name 
    AND YEAR(CAST(A.ym+'01' AS datetime))=YEAR(CAST(C.ym+'01' AS datetime))+1
    AND MONTH(CAST(A.ym+'01' AS datetime))=MONTH(CAST(C.ym+'01' AS datetime))

DROP TABLE pierc

[xman_78tom]

-- 环比
select p1.[name],p1.[ym],p2.[ym],
cast((p2.[money]-p1.[money])/p1.[money] as numeric(18,2))
from [pierc] p1 inner join [pierc] p2
on dateadd(mm,1,cast(p1.[ym]+'01' as datetime))=p2.[ym]+'01' 
and p1.[name]=p2.[name]
/*
1	200801	200802	1.15
1	200802	200803	4.24
2	200801	200802	17.00
*/
-- 同比
select p1.[name],p1.[ym],p2.[ym],
cast((p2.[money]-p1.[money])/p1.[money] as numeric(18,2))
from [pierc] p1 inner join [pierc] p2
on dateadd(yy,1,cast(p1.[ym]+'01' as datetime))=p2.[ym]+'01'
and p1.[name]=p2.[name]
/*
1	200801	200901	0.00
2	200802	200902	1.00
*/

[feixianxxx]

如果当中隔掉几个月呢？

[renadg]

隔月也是要算进去的..一样的

[feixianxxx]

select '1','200803','991','' union
select '1','200805','881',''
隔月 用 (881-991)/991 ?

[xman_78tom]

-- 环比
;with t as(
select *,rid=row_number() over (partition by [name] order by [ym]) from [pierc]
)
select t1.[name],t1.[ym],t2.[ym],
cast((t2.[money]-t1.[money])/t1.[money] as numeric(18,2))
from t t1 inner join t t2 on t1.[name]=t2.[name] and t1.rid+1=t2.rid