求按周显示库存的代码

Question

1010101010018 需求量   0     2008-8-6
1010101010018 需求量   12     2008-8-7
1010101010018 需求量   0     2008-8-15
1010101010018 需求量   60       2008-8-19
1010101010018 采购订单 10       2008-8-6
1010101010018 采购订单 10       2008-8-8
1010101010018 采购订单 30       2008-8-17
1010101010018 采购申请 2     2008-8-6
1010101010018 采购申请 50       2008-8-11
1010101010018 采购申请 50       2008-8-20
上表是原始数据，想求一段代码得出如下结果：
1010101010018 需求量   0     2008-8-6
1010101010018 需求量   12     2008-8-13
1010101010018 需求量   60     2008-8-20
1010101010018 采购订单 10       2008-8-6
1010101010018 采购订单 10       2008-8-13
1010101010018 采购订单 30       2008-8-20
1010101010018 采购申请 2     2008-8-6
1010101010018 采购申请 50       2008-8-13
1010101010018 采购申请 50       2008-8-20
请大侠们给予帮助！定感激不进啊！

Answer 1

select
    itemid -- like '1010101010018'
   ,type -- in ('需求量','采购订单')
   ,amount = sum(amount)
   ,wday = dateadd(week,datediff(week,0,theday),0)+2
from thetable
group by
    itemid
   ,type 
   ,dateadd(week,datediff(week,0,theday),0)+2

Answer 2

create table tmp(cCode varchar(20),cType varchar(20),cNum int,cDate datetime)
insert into tmp values('1010101010018','需求量  ',0 ,'2008-08-06') 
insert into tmp values('1010101010018','需求量  ',12,'2008-08-07') 
insert into tmp values('1010101010018','需求量  ',0 ,'2008-08-15') 
insert into tmp values('1010101010018','需求量  ',60,'2008-08-19') 
insert into tmp values('1010101010018','采购订单',10,'2008-08-06') 
insert into tmp values('1010101010018','采购订单',10,'2008-08-08') 
insert into tmp values('1010101010018','采购订单',30,'2008-08-17') 
insert into tmp values('1010101010018','采购申请',2 ,'2008-08-06') 
insert into tmp values('1010101010018','采购申请',50,'2008-08-11') 
insert into tmp values('1010101010018','采购申请',50,'2008-08-20')
godeclare @sDate datetime,@eDate datetime,@i int
declare @t1 table(tDate datetime)
declare @t2 table(code varchar(20),ctype varchar(20),cnum int,cdate datetime)select @sDate=min(cDate),@eDate=max(cDate) from tmpif (datediff(dd,@sDate,@eDate)+1)%7=0
    set @i=datediff(dd,@sDate,@eDate)/7+1
else
    set @i=datediff(dd,@sDate,@eDate)/7while @i>=0
begin
    insert into @t1 select dateadd(ww,@i,@sDate)
    set @i=@i-1
endselect
    d.cCode,d.cType,d.cNum,a.tDate  
from 
    @t1 a,
    (select distinct cType from tmp) b,
    (select distinct cCode from tmp) c,
    tmp d
where
    a.tDate>=d.cDate and b.cType=d.cType and c.cCode=d.cCode
    and
    not exists(select 1 from tmp where cDate>d.cDate and cDate<=a.tDate and cType=d.cType and cCode=d.cCode)
order by
    d.cCode,d.cType,a.tDate/*
cCode                cType                cNum        tDate                                                  
-------------------- -------------------- ----------- ------------------------------------------------------ 
1010101010018        采购订单                 10          2008-08-06 00:00:00.000
1010101010018        采购订单                 10          2008-08-13 00:00:00.000
1010101010018        采购订单                 30          2008-08-20 00:00:00.000
1010101010018        采购申请                 2           2008-08-06 00:00:00.000
1010101010018        采购申请                 50          2008-08-13 00:00:00.000
1010101010018        采购申请                 50          2008-08-20 00:00:00.000
1010101010018        需求量                  0           2008-08-06 00:00:00.000
1010101010018        需求量                  12          2008-08-13 00:00:00.000
1010101010018        需求量                  60          2008-08-20 00:00:00.000
*/godrop table tmp
go