Untitled

                Never    
SQL
       
---一、R5 数据清洗

create table wastebook201905_r5 as 
select shopid, goodsid, sum(directflag * closeqty) qty
               from wastebook201905@r5sd
              where sheettype = '6666'
                and shopid like '4%'
              group by goodsid, shopid
              having sum(directflag * closeqty)<>0

SELECT * FROM wastebook201905_r5  ;

---4024 4025 4032 转换成B00L

SELECT * FROM wastebook201905_r5  ;
update wastebook201905_r5 set shopid='B00L' where shopid in ('4024','4025','4032');

---将商品编码转换成R6商品编码

 update wastebook201905_r5 b set goodsid=( SELECT goodsidnew FROM (
 SELECT a.goodsid,b.goodsid goodsidnew FROM goodsr5 a ,goodsr6 b WHERE a.name=b.name and a.goodsid<>b.goodsid) a
 where a.goodsid=b.goodsid
 )
 where exists ( SELECT goodsidnew FROM (
 SELECT a.goodsid,b.goodsid goodsidnew FROM goodsr5 a ,goodsr6 b WHERE a.name=b.name and a.goodsid<>b.goodsid) a where 
 a.goodsid=b.goodsid
 )
 
 ---数据重新汇总
 
 create table wastebook_r5
 as 
 
 SELECT shopid,goodsid,sum(qty)qty FROM wastebook201905_r5 group by shopid,goodsid;
 
 
--二、R6数据准备

create table wastebook_r6 as 
select shopid, goodsid, sum(directflag * qty) qty
          from wastebook
         where sheettype = '2444'
         group by goodsid, shopid 
 
 ---二、数据比较
 
 ----1、1 r5有,r6没有 ,漏导
 select shopid, goodsid, sum(qty) qty
   from wastebook_r5
  where (shopid, goodsid) not in (select shopid, goodsid from wastebook_r6)
  group by shopid, goodsid
 having sum(qty) > 0;
---1、2 R5 负库存部分 未导
 select shopid, goodsid, sum(qty) qty
   from wastebook_r5
  where (shopid, goodsid) not in (select shopid, goodsid from wastebook_r6)
  group by shopid, goodsid
 having sum(qty) < 0;
 
 
 ---2.1 r6导入商品,r5没有
select shopid, goodsid, sum(qty) qty
  from wastebook_r6
 where (shopid, goodsid) not in
       (select shopid, goodsid from wastebook_r5 group by shopid, goodsid)
 group by shopid, goodsid
having sum(qty) < 0;
 
--3.1 导入部分有差异,多导 

SELECT a.shopid,
       a.goodsid,
      sum(a.qty)qty5,
      sum(  b.qty) qty6 ,
      sum(nvl(  b.qty,0)) - sum(nvl(a.qty,0)) cy
  FROM wastebook_r5 a, wastebook_r6 b
 WHERE   a.goodsid = b.goodsid
   and a.shopid = b.shopid
 group by a.shopid, a.goodsid
 having sum(nvl(  b.qty,0)) - sum(nvl(a.qty,0)) >0

---3.1导入部分,少导
SELECT a.shopid,
       a.goodsid,
      sum(a.qty)qty5,
      sum(  b.qty) qty6 ,
      sum(nvl(  b.qty,0)) - sum(nvl(a.qty,0)) cy
  FROM wastebook_r5 a, wastebook_r6 b
 WHERE   a.goodsid = b.goodsid
   and a.shopid = b.shopid
 group by a.shopid, a.goodsid
 having sum(nvl(  b.qty,0)) - sum(nvl(a.qty,0)) <0
 
 ---4、处理分析
 --4.1 R5有,R6没有的部分,正库存 18795  负库存 20400.66 合计 -1605 未导入,建议不导入,总数为负,负库存不进系统
 --4.2 R6有,R5没有,这部分当做正常盘点业务,数据不统计至期初
 --4.3 多导部分,库存多导数量在 225508.8,这部分数据需要剥离掉,统计进期初,否则期初增加,剥离的多导入部分,当做门店正常盘点业务产生
 --4.4 少导部分,库存导入数量小于R5数量,10824.686个,建议盘点进系统参与期初统计。
 
 
 
 ---5、数据处理,主要处理4.3多导入导部分剥离
 
--剥离283225 ,中间有重复,去掉重复部分,需人工剥离出225508.8
 SELECT   b.* FROM 
(SELECT a.shopid,
       a.goodsid,
      sum(a.qty)qty5,
      sum(  b.qty) qty6 ,
      sum(nvl(  b.qty,0)) - sum(nvl(a.qty,0)) cy
  FROM wastebook_r5 a, wastebook_r6 b
 WHERE   a.goodsid = b.goodsid
   and a.shopid = b.shopid
 group by a.shopid, a.goodsid
 having sum(nvl(  b.qty,0)) - sum(nvl(a.qty,0)) >0)a,wastebook b
 where a.goodsid=b.goodsid and a.shopid=b.shopid and a.cy=b.qty
 and b.sheettype=2444;

Raw Text