统计一表中上年和本年同时间段累计数据问题
统计一表中上年和本年同时间段累计数据问题
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
[解决办法]
如果是一月份一次,三月份三次
那你就用循环啊,用月份做循环值。
declare @i intset @i=1while @i<=3begin.......//你的统计语句end
[解决办法]
忘了累加了declare @i intset @i=1while @i<=3--3表示你的月份begin.......--你的统计语句set @i=@i+1end
[解决办法]
--> 测试数据: #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*/