统计一表中上年和本年同时间段累计数据问题
统计一表中上年和本年同时间段累计数据问题

info
f27 f28 f8 dw tbsj
2 1 1 A 2007-01-01
2 1 1 B 2007-01-01
2 1 1 A 2007-02-01
2 1 1 B 2007-02-01

2 1 1 A 2008-01-01
2 1 1 B 2008-01-01
2 1 1 A 2008-02-01
2 1 1 B 2008-02-01

累计2月份生成报表：
累计2月份生成报表：
dw f27本年 f28本年 f8本年 f27上年 f28上年 f8上年
A 4 2 2 4 2 2
B 4 2 2 4 2 2

以下代码，当选择2月份重复2次统计，选择3月份重复3次统计。
请求帮助，谢谢


SELECT ThisY.tbdw as tbdw,
sum(ThisY.f27) as ThisYf27,sum(ThisY.f28) as ThisYf28,sum(ThisY.f8) as ThisYf8,
sum(PrevY.f27) as PrevYf27,sum(PrevY.f28) as PrevYf28,sum(PrevY.f8) as PrevYf8,
(sum(ThisY.f27)-sum(PrevY.f27))/sum(PrevY.f27) as ywsrl,
(sum(ThisY.f8)-sum(PrevY.f8))/sum(PrevY.f8) as mssrl,
(sum(ThisY.f28)-sum(PrevY.f28))/sum(PrevY.f28) as jssrl
FROM info ThisY INNER JOIN info PrevY ON ThisY.tbdw=PrevY.tbdw
WHERE
datepart(year,ThisY.tbsj)= datepart(year,@tbsj)
and (Month(ThisY.tbsj) BETWEEN 1 AND Month(@tbsj))
and datepart(year,PrevY.tbsj) =(datepart(year,@tbsj)-1)
and (Month(PrevY.tbsj) BETWEEN 1 AND Month(@tbsj))

and (ThisY.tbdw='A单位' or
ThisY.tbdw='B单位' or
ThisY.tbdw='C单位'
)
group by ThisY.tbdw

[解决办法]
如果是一月份一次，三月份三次
那你就用循环啊，用月份做循环值。

SQL code

declare @i intset @i=1while @i<=3begin.......//你的统计语句end
[解决办法]
SQL code
忘了累加了declare @i intset @i=1while @i<=3--3表示你的月份begin.......--你的统计语句set @i=@i+1end
[解决办法]
SQL code
--> 测试数据: #if object_id('tempdb.dbo.#') is not null drop table #create table # (f27 int,f28 int,f8 int,dw varchar(1),tbsj datetime)insert into #select 2,1,1,'A','2007-01-01' union allselect 2,1,1,'B','2007-01-01' union allselect 2,1,1,'A','2007-02-01' union allselect 2,1,1,'B','2007-02-01' union allselect 2,1,1,'A','2008-01-01' union allselect 2,1,1,'B','2008-01-01' union allselect 2,1,1,'A','2008-02-01' union allselect 2,1,1,'B','2008-02-01'declare @tbsj datetimeset @tbsj = '20080301'select    dw=isnull(a.dw,b.dw),    f27本年=a.f27,    f28本年=a.f28,    f8本年=a.f8,    f27上年=b.f27,    f28上年=b.f28,    f8上年=b.f8from    (        select dw,sum(f27)f27,sum(f28)f28,sum(f8)f8 from #        where year(tbsj)=year(@tbsj) and month(tbsj)<=month(@tbsj)        group by dw    ) afull join    (        select dw,sum(f27)f27,sum(f28)f28,sum(f8)f8 from #        where year(tbsj)=year(@tbsj)-1 and month(tbsj)<=month(@tbsj)        group by dw    ) bon a.dw=b.dw/*dw   f27本年       f28本年       f8本年        f27上年       f28上年       f8上年---- ----------- ----------- ----------- ----------- ----------- -----------A    4           2           2           4           2           2B    4           2           2           4           2           2*/