22,207
社区成员
发帖
与我相关
我的任务
分享
if(exists(select * from sysobjects where name='proc_stock'))
drop proc proc_stock
go
create proc proc_stock
as
declare @stardate datetime
declare @d datetime
declare @i int
declare @begindate datetime
declare @enddate datetime
begin
set @d=getdate()
set @i=0
select @stardate=dateadd(wk,datediff(wk,0,@d),0)
while @i<96
begin
set @begindate=dateadd(day,-(@i*7),@stardate)
if @i<>0
begin
set @enddate=dateadd(day,-(@i-1)*7-1,@stardate)
end
else
begin
set @enddate=@d
end
select sum(price) from stock where tdate
between @begindate and @enddate
set @i=@i+1
end
end
select top 96 sum(price),datepart(wk,tdate), SubStr(tdate, 0, 7)
from stock
group by SubStr(tdate, 0, 7), datepart(wk,tdate)
order by SubStr(tdate, 0, 7) desc, datepart(wk, tdate) desc
select sum(price),datepart(wk,tdate)
from stock
group by datepart(wk,tdate)