34,838
社区成员




declare @Date1 datetime,@Date2 datetime, @Day varchar(2)
set @Date1='20080301'
set @Date2=dateadd(month,1,@Date1)
declare @SQL varchar(8000)
set @SQL='select 物料=b.wlid,供应商=a.gysid'
set @SQL=@SQL+',上旬=sum(case (day(b.日期)-1)/10 when 0 then b.dhsl else 0 end)'
set @SQL=@SQL+',中旬=sum(case (day(b.日期)-1)/10 when 1 then b.dhsl else 0 end)'
set @SQL=@SQL+',下旬=sum(case when (day(b.日期)-1)/10>1 then b.dhsl else 0 end)'
set @SQL=@SQL+',总数=sum(b.dhsl)'
set @Day=1
while @Day<=day(@Date2-1)
begin
set @SQL=@SQL+',['+@Day+']=sum(case day(b.日期) when '+@Day+' then b.dhsl else 0 end)'
set @Day=@Day+1
end
set @SQL=@SQL+' from cggl_cgdd as a join cggl_cgddmxb as b on a.fid=b.cgddid'
set @SQL=@SQL+' where b.日期>='''+convert(varchar,@Date1,121)+''''
set @SQL=@SQL+' and b.日期<'''+convert(varchar,@Date2,121)+''' group by b.wlid,a.gysid'
/*
表1 cggl_cgdd
fid int primary key,
rq datetime(8),
gysid ,int ---供应商编码
.....
表2 cggl_cgddmxb
fid int primary ,
cgddid int ,---fk ---外码 与cggl_cgdd fid 关联
wlid int, ----物料编码
dhsl numeric ,
rq datetime
要求一个月内的物料供应信息
物料 供应商 上旬 中旬 下旬 总数 1 2 3 .....31
*/
select 物料,
供应商,
datepart(m,rq) [month],
上旬= sum(case when day(rq) between 1 and 10 then dhsl else 0 end),
中旬= sum(case when day(rq) between 10 and 20 then dhsl else 0 end),
上旬= sum(case when day(rq)>20 then dhsl else 0 end),
总数 = sum(dhsl),
[1]=sum(case when day(rq)= 1 then dhsl else 0 end),
[2]=sum(case when day(rq)= 2 then dhsl else 0 end),
..................
from ta1 a,ta2 b
where a.gysid = b.gysid
group by 物料,
供应商,
datepart(m,rq)