【数据处理】大库订货数据匹配

//DM商品

update BASPLUMAIN set CURCSPRC=b.curcsprc from BASPLUMAIN a,[192.168.186.2].[scmis].dbo.商品码A B WHERE A.PLUNO=B.PLUNO
update BASPLUMAIN set CURCSPRC=b.curcsprc from BASPLUMAIN a,[192.168.4.100].[scmis].dbo.商品码A B WHERE A.PLUNO=B.PLUNO
update BASPLUMAIN set CURCSPRC=b.curcsprc from BASPLUMAIN a,[192.168.2.102].[scmis].dbo.商品码A B WHERE A.PLUNO=B.PLUNO
update BASPLUMAIN set CURCSPRC=b.curcsprc from BASPLUMAIN a,[192.168.1.100].[scmis].dbo.商品码A B WHERE A.PLUNO=B.PLUNO

select distinct a.货号,a.品名+A.规格,a.规格,a.配送含量,a.最终进价 大库价格,c.curcsprc 进价,a.最终进价-c.curcsprc 差价,
case when d.qty is null then 0 else d.qty end +case when e.qty is null then 0 else e.qty end +case when f.qty is null then 0 else f.qty end +case when g.qty is null then 0 else g.qty end+case when h.qty is null then 0 else h.qty end 总销售,
case when a1.qty is null then 0 else a1.qty end + case when a2.qty is null then 0 else a2.qty end +case when a3.qty is null then 0 else a3.qty end + case when a4.qty is null then 0 else a4.qty end+ case when a5.qty is null then 0 else a5.qty end 总库存,
d.qty 西部销售,e.qty 东部销售,f.qty 中区销售,g.qty 技校销售,h.qty 团购销售,
a1.qty 西部库存,a2.qty 东部库存,a3.qty 中区库存,a4.qty 技校库存,a5.qty 团购库存,b.lhflm 分类码,/*a.公司让扣,*/a.供应商让扣,a.买赠
from lhdh201503DM a
left join lhspm b on a.货号=b.pluid
left join 商品码B c on b.barcode=c.BCD
left join (select b.PLUNO,SUM(b.QTY) qty from DOC21BF a,DOC21DT b where a.COMNO is null and a.DOCNO=b.DOCNO and SUBSTRING(a.DOCNO,1,4)='6015' and a.DOCDAT>='20140301' and a.DOCDAT<='20140430' group by b.PLUNO) d on c.pluno=d.pluno
left join (select b.PLUNO,SUM(b.QTY) qty from DOC21BF a,DOC21DT b where a.COMNO is null and a.DOCNO=b.DOCNO and SUBSTRING(a.DOCNO,1,4)='7606' and a.DOCDAT>='20140301' and a.DOCDAT<='20140430' group by b.PLUNO) e on c.pluno=e.pluno
left join (select b.PLUNO,SUM(b.QTY) qty from DOC21BF a,DOC21DT b where a.COMNO is null and a.DOCNO=b.DOCNO and SUBSTRING(a.DOCNO,1,4)='0003' and a.DOCDAT>='20140301' and a.DOCDAT<='20140430' group by b.PLUNO) f on c.pluno=f.pluno
left join (select b.PLUNO,SUM(b.QTY) qty from DOC21BF a,DOC21DT b where a.COMNO is null and a.DOCNO=b.DOCNO and SUBSTRING(a.DOCNO,1,4)='0007' and a.DOCDAT>='20140301' and a.DOCDAT<='20140430' group by b.PLUNO) g on c.pluno=g.pluno
left join (select b.PLUNO,SUM(b.QTY) qty from DOC21BF a,DOC21DT b where a.DOCNO=b.DOCNO and SUBSTRING(a.DOCNO,1,4)='0008' and a.DOCDAT>='20140301' and a.DOCDAT<='20140430' group by b.PLUNO) h on c.pluno=h.pluno
left join (select pluno,qty from sskc where mddm='6015') a1 on c.pluno=a1.pluno
left join (select pluno,qty from sskc where mddm='7606') a2 on c.pluno=a2.pluno
left join (select pluno,qty from sskc where mddm='0003') a3 on c.pluno=a3.pluno
left join (select pluno,qty from sskc where mddm='0007') a4 on c.pluno=a4.pluno
left join (select pluno,qty from sskc where mddm='0008') a5 on c.pluno=a5.pluno
order by b.lhflm

 

 

 

//总清单

select distinct a.商品编码,a.品名,a.规格,a.配送含量,a.最终进价 大库价格,c.curcsprc 进价,a.最终进价-c.curcsprc 差价,
case when d.qty is null then 0 else d.qty end +case when e.qty is null then 0 else e.qty end +case when f.qty is null then 0 else f.qty end +case when g.qty is null then 0 else g.qty end+case when h.qty is null then 0 else h.qty end 总销售,
case when a1.qty is null then 0 else a1.qty end + case when a2.qty is null then 0 else a2.qty end +case when a3.qty is null then 0 else a3.qty end + case when a4.qty is null then 0 else a4.qty end+ case when a5.qty is null then 0 else a5.qty end 总库存,
d.qty 西部销售,e.qty 东部销售,f.qty 中区销售,g.qty 技校销售,h.qty 团购销售,
a1.qty 西部库存,a2.qty 东部库存,a3.qty 中区库存,a4.qty 技校库存,a5.qty 团购库存,b.lhflm 分类码,a.公司让扣,a.供应商让扣,a.买赠,a.备注
from lhdh201412 a
left join lhspm b on a.商品编码=b.pluid
left join 商品码B c on b.barcode=c.BCD
left join (select b.PLUNO,SUM(b.QTY) qty from DOC21BF a,DOC21DT b where a.COMNO is null and a.DOCNO=b.DOCNO and SUBSTRING(a.DOCNO,1,4)='6015' and a.DOCDAT>='20141015' and a.DOCDAT<='20141129' group by b.PLUNO) d on c.pluno=d.pluno
left join (select b.PLUNO,SUM(b.QTY) qty from DOC21BF a,DOC21DT b where a.COMNO is null and a.DOCNO=b.DOCNO and SUBSTRING(a.DOCNO,1,4)='7606' and a.DOCDAT>='20141015' and a.DOCDAT<='20141129' group by b.PLUNO) e on c.pluno=e.pluno
left join (select b.PLUNO,SUM(b.QTY) qty from DOC21BF a,DOC21DT b where a.COMNO is null and a.DOCNO=b.DOCNO and SUBSTRING(a.DOCNO,1,4)='0003' and a.DOCDAT>='20141015' and a.DOCDAT<='20141129' group by b.PLUNO) f on c.pluno=f.pluno
left join (select b.PLUNO,SUM(b.QTY) qty from DOC21BF a,DOC21DT b where a.COMNO is null and a.DOCNO=b.DOCNO and SUBSTRING(a.DOCNO,1,4)='0007' and a.DOCDAT>='20141015' and a.DOCDAT<='20141129' group by b.PLUNO) g on c.pluno=g.pluno
left join (select b.PLUNO,SUM(b.QTY) qty from DOC21BF a,DOC21DT b where a.DOCNO=b.DOCNO and SUBSTRING(a.DOCNO,1,4)='0008' and a.DOCDAT>='20141015' and a.DOCDAT<='20141129' group by b.PLUNO) h on c.pluno=h.pluno
left join (select pluno,qty from sskc where mddm='6015') a1 on c.pluno=a1.pluno
left join (select pluno,qty from sskc where mddm='7606') a2 on c.pluno=a2.pluno
left join (select pluno,qty from sskc where mddm='0003') a3 on c.pluno=a3.pluno
left join (select pluno,qty from sskc where mddm='0007') a4 on c.pluno=a4.pluno
left join (select pluno,qty from sskc where mddm='0008') a5 on c.pluno=a5.pluno
order by b.lhflm

 

 

select distinct a.商品编码,a.品名,a.规格,a.配送含量,a.最终进价 大库价格,c.curcsprc 进价,a.最终进价-c.curcsprc 差价,
d.qty 销售,e.qty 库存
,b.lhflm 分类码,a.公司让扣,a.供应商让扣,a.买赠,a.备注
from lhdh201508 a
left join lhspm b on a.商品编码=substring(b.pluid,9,6)
left join 商品码B c on b.barcode =cast(c.BCD as varchar(13))
left join nlgpossale('20150701','20150818') d on c.pluno=d.pluno
left join (select pluno,qty from 实时库存 ) e on c.pluno=e.pluno