Untitled
Never
---一、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
-
"PYTHAGOREAN THEOREM" TEACHER FANTASER FUCKED A STUDENT RIGHT IN LESSON _ NIGONIKA PORN 20
21 min ago
-
No Deposit Bonus
33 min ago
-
Adult Telegram Channels
35 min ago
-
https://www.facebook.com/FitSpressoPrice/
46 min ago
-
Perky Nipples Compilation - Avery Black, Val Steele, Binky Baez, Destiny Cruz & Alexia Anders
56 min ago
-
Sweet Relief: Why Bliss Bites CBD Gummies Are the Ultimate Treat
59 min ago
-
https://www.facebook.com/SuperiorCBDGummiesInCa/
1 hour ago
-
Makers CBD Gummies(2024) 100% Safe, Does It Really Work Or Not?
1 hour ago
-
MOFOS - Amy Amor Tells Jordi El His Idea Of What She Should Do On Her First Blind Date
1 hour ago
-
https://www.facebook.com/SuperiorCBDGummiesInCa/
1 hour ago