表 test
time mumber
2006-03-01 3
2006-03-02 2
2006-03-03 5
2007-03-01 8
2007-03-02 1
2007-03-03 2用户输入时间段2007-03-01到2007-03-03
要求结果:
number TB
11 110%
其中TB等于用户输入的时间段中number的和减去去年同期number的和乘以100%也就是算个同期比
望高手们不吝赐教小弟在这里先谢谢各位了
time mumber
2006-03-01 3
2006-03-02 2
2006-03-03 5
2007-03-01 8
2007-03-02 1
2007-03-03 2用户输入时间段2007-03-01到2007-03-03
要求结果:
number TB
11 110%
其中TB等于用户输入的时间段中number的和减去去年同期number的和乘以100%也就是算个同期比
望高手们不吝赐教小弟在这里先谢谢各位了
@begindate datetime
@enddate datetime
as
declare @oldsum int
select @oldsum =sum(mumber) from tb where time between dateadd(year,-1,@begindate) and dateadd(year,-1,@enddate)
select sum(mumber) number,tb=cast((sum(mumber)-@oldsum )*1.0/oldsum as varchar)+'%' from tb where time between @begindate and @enddate
([time] Varchar(10),
number Int)
Insert test Select '2006-03-01', 3
Union All Select '2006-03-02', 2
Union All Select '2006-03-03', 5
Union All Select '2007-03-01', 8
Union All Select '2007-03-02', 1
Union All Select '2007-03-03', 2
GO
Declare @StartDate DateTime, @EndDate DateTime
Select @StartDate = '2007-03-01', @EndDate = '2007-03-03'
Select
number,
Rtrim(Cast((number - LastYearnumber) * 100.0 / LastYearnumber As Int)) + '%' As TB
From
(
Select
SUM(number) As number,
(Select SUM(number) From test Where [time] Between DateAdd(yy, -1, @StartDate) And DateAdd(yy, -1, @EndDate)) As LastYearnumber
From
test
Where [time] Between @StartDate And @EndDate
) A
GO
Drop Table test
--Result
/*
number TB
11 10%
*/
drop table tb
gocreate table tb(time datetime,number int)
insert into tb(time,number) values('2006-03-01', 3)
insert into tb(time,number) values('2006-03-02', 2)
insert into tb(time,number) values('2006-03-03', 5)
insert into tb(time,number) values('2007-03-01', 8)
insert into tb(time,number) values('2007-03-02', 1)
insert into tb(time,number) values('2007-03-03', 2)
godeclare @time1 as datetime
declare @time2 as datetime
set @time1 = '2007-03-01'
set @time2 = '2007-03-03'select m.number , cast(cast((m.number*1.00 - n.number)/n.number*100 as decimal(18,2)) as varchar) + '%' TB from
(select sum(number) as number from tb where time >= @time1 and time <= @time2) m,
(select sum(number) as number from tb where time >= dateadd(year, -1 , @time1) and time <= dateadd(year , -1 ,@time2)) n
drop table TB/*
number TB
----------- -------------------------------
11 10.00%(所影响的行数为 1 行)
*/
([time] Varchar(10),
number Int)
Insert test Select '2006-03-01', 3
Union All Select '2006-03-02', 2
Union All Select '2006-03-03', 5
Union All Select '2007-03-01', 8
Union All Select '2007-03-02', 1
Union All Select '2007-03-03', 2declare @begin varchar(30)
declare @end varchar(30)
set @begin='2007-03-01'
set @end='2007-03-03'select sum(number) as number ,rtrim(
(sum(number)/((select sum(number)
from test where time between dateadd(yy,-1,@begin) and
dateadd(yy,-1,@end))+0.00))*100)+'%' from test
where time between @begin and @end