34,576
社区成员
发帖
与我相关
我的任务
分享
select a.dCreateDate,a.prshulian ,b.slshulian ,c.delshulian,d.backshulian,e.inshulian,f.bfshulian
from
(
select Convert(varchar(10),tr.dCreateDate,120) as dCreateDate,sum(tr.shulian) as prshulian--目前库存数量
from jxc_r_stock_report tr
where tr.title like '%DA07%' and tr.dCreateDate >='2011-05-12' and tr.dCreateDate <='2011-06-30'
group by Convert(varchar(10),tr.dCreateDate,120)
) a
left join
(
select Convert(varchar(10),selldate,120) as selldate,sum(shulian) as slshulian--销售数量
from sell
where title like '%DA07%' and isok>=0 and type=0 and selldate >='2011-05-12' and selldate <='2011-06-30'
group by Convert(varchar(10),selldate,120)
) b on a.dCreateDate = b.selldate
left join
(
select Convert(varchar(10),selldate,120) as selldate,sum(shulian) as delshulian--删单数量
from sell
where title like '%DA07%' and isok=-1 and selldate >='2011-05-12' and selldate <='2011-06-30'
group by Convert(varchar(10),selldate,120)
) c on a.dCreateDate = c.selldate
left join
(
select Convert(varchar(10),selldate,120) as selldate,sum(shulian) as backshulian--退回数量
from sell
where title like '%DA07%' and type=2 and selldate >='2011-05-12' and selldate <='2011-06-30'
group by Convert(varchar(10),selldate,120)
) d on a.dCreateDate = d.selldate
left join
(
select Convert(varchar(10),selldate,120) as selldate,sum(shulian) as inshulian--入库数量
from buy
where title like '%DA07%' and type=0 and zu=0 and selldate >='2011-05-12' and selldate <='2011-06-30'
group by Convert(varchar(10),selldate,120)
) e on a.dCreateDate =e.selldate
left join
(
select Convert(varchar(10),selldate,120) as selldate,sum(shulian) as bfshulian--报废数量
from buy
where title like '%DA07%' and type=1 and selldate >='2011-05-12' and selldate <='2011-06-30'
group by Convert(varchar(10),selldate,120)
) f on a.dCreateDate =f.selldate
order by a.dCreateDate