【数据处理】大库订货会数据处理

SELECT * FROM  LHDH201412DM

--DM商品处理

alter table dbo. LHDH201412DM
alter column 货号 varchar(10)

 

update LHDH201412DM set 货号='0'+SUBSTRING(货号,1,5) WHERE LEN(货号)=5

update LHDH201412DM set 货号='00'+SUBSTRING(货号,1,4) WHERE LEN(货号)=4

update LHDH201412DM set 货号='000'+SUBSTRING(货号,1,3) WHERE LEN(货号)=3

update LHDH201412DM set 货号='0000'+SUBSTRING(货号,1,2) WHERE LEN(货号)=2

update LHDH201412DM set 货号='000000'+SUBSTRING(货号,1,1) WHERE LEN(货号)=1

 

update LHDH201412DM set 促销价=原价 where 促销价 is null

update LHDH201412DM set 公司让扣=0 where 公司让扣 is null

update LHDH201412DM set 供应商让扣=0 where 供应商让扣 is null

ALTER TABLE LHDH201412DM ADD 最终进价 decimal(8,4)

update LHDH201412DM set 最终进价=促销价*(100-供应商让扣)/100

update LHDH201412DM set 最终进价=促销价*(100-公司让扣-供应商让扣)/100

--总清单

select * from lhdh201503

alter table dbo. LHDH201503
alter column 货号 varchar(10)

UPDATE LHDH201503 SET 货号=SUBSTRING(货号,2,6)

update LHDH201503 set 货号='0'+SUBSTRING(货号,1,5) WHERE LEN(货号)=5

update LHDH201503 set 货号='00'+SUBSTRING(货号,1,4) WHERE LEN(货号)=4

update LHDH201503 set 货号='000'+SUBSTRING(货号,1,3) WHERE LEN(货号)=3

update LHDH201503 set 货号='0000'+SUBSTRING(货号,1,2) WHERE LEN(货号)=2

update LHDH201503 set 货号='000000'+SUBSTRING(货号,1,1) WHERE LEN(货号)=1

 

update LHDH201503 set 促销进价=原进价 where 促销进价 is null

update LHDH201503 set 促销配销价=促销进价 where 促销配销价 is null

update LHDH201503 set 公司让扣=0 where 公司让扣 is null

update LHDH201503 set 供应商让扣=0 where 供应商让扣 is null

ALTER TABLE LHDH201503 ADD 最终进价 decimal(8,4)

update LHDH201503 set 最终进价=促销配销价*(100-公司让扣-供应商让扣)/100

 

 

 

 

 

 

UPDATE LHSPM2 SET PLUID=SUBSTRING(PLUID,9,6)


DELETE FROM LHSPM

INSERT INTO LHSPM
SELECT PLUID,MAX(PLUNAME),MAX(PLUUNIT),BARCODE,MAX(LHFLM),MAX(SPEC),MAX(PLUBRND)
FROM LHSPM2
GROUP BY PLUID,BARCODE

 

 

 

select distinct a.商品编码,a.品名,a.规格,a.配送含量,a.最终进价 大库价格,c.curcsprc 进价,a.最终进价-c.curcsprc 差价,
d.qty 销售,a5.qty 库存,b.lhflm 分类码,a.公司让扣,a.供应商让扣,a.买赠,a.备注
from lhdh201510 a
left join lhspm b on a.商品编码=b.pluid
left join (select * from mdspm where mddm='9772') c on b.barcode=c.BCD
left join (select PLUNO,qty from mdxs9772) d on c.pluno=d.pluno
left join (select pluno,qty from sskc where mddm='9772') a5 on c.pluno=a5.pluno
order by b.lhflm