库存变动

—Version1
SELECT
inco AS ‘商品编码’
, qty AS ‘出入库树数量’—（-出）
, biid AS ‘单据编号’
, soco AS ‘来源单号’
, fwid AS ‘库位编码’
, whco AS ‘仓库编码’
, crna AS ‘操作人’
, crdt AS ‘操作时间’
, rema AS ‘备注’
, dety AS ‘业务类型’
,
case
when buty = ‘STOCKOUT’ then ‘出库单’
when buty = ‘move’ then ‘移库单’
when buty = ‘poin’ then ‘入库单’
when buty = ‘otherin’ then ‘其他入库单’
when buty = ‘TRANOUT’ then ‘调拨出库单’
when buty = ‘HP’ then ‘跨仓移库单’
when buty = ‘TRANIN’ then ‘调拨入库单’
when buty = ‘otherout’ then ‘其他出库单’
when buty = ‘PICKDOWN’ then ‘拣货下架单’

else ‘其他’ end
as ‘模块类型’

FROM stlo20200929
WHERE 1=1
AND crdt>= ‘2020/1/1 00:00:00’AND crdt<= ‘2020/7/1 00:00:00’
AND inco=’SC19032553’
AND whco=’U01’
AND buty not in (‘移库单’,’拣货下架单’)

—Version2**
SELECT v.inna as’商品名称’,a.inco as’商品编码’,v.tyco AS ‘货号’,v.inse AS ‘规格’,a.whco AS ‘仓库’—商品、货号、规格、仓库
,a.buty AS ‘模块’,CASE WHEN m.mona=’入库单’ THEN ‘采购入库单’ WHEN m.mona=’出库单’ THEN ‘销售出库单’ WHEN m.mona is null THEN ‘混批次增减库存’ ELSE m.mona END AS ‘模块名称’—模块
,case when a.qty>0 then isnull(a.qty,0) END AS ‘入库数量’
,CASE WHEN a.qty<0 THEN ISNULL(a.qty,0) END AS '出库数量'
,isnull(a.fwid,a.owid) as ‘库位编码’,w.whna as’库位名称’
,a.biid AS ‘单据号’,a.soty AS ‘来源类型’,m1.mona AS ‘来源类型’,a.soco AS ‘来源单号’
,convert(nvarchar(10),a.crdt,23)
,a.crna,w.whco,a.rema AS ‘备注’
FROM stlo20200929 a
left join modu m on m.moid = a.buty
left join modu m1 on m1.moid = a.soty
left join waho w on isnull(a.fwid,a.owid) = w.whid
left join inve v on v.inco = a.inco
left join owin ow on a.owco = ow.owco
left join owin o on a.cotl = o.cotl
left join btin bt on bt.btno=a.btno and bt.inco=v.inco
where 1=1 and
a.buty in (—‘PICKDOWN’,’HP’,’move’—模块 排除拣货下架、移库
‘poin’,’otherin’,’STOCKOUT’,’overflow’,’SHEFT’,’ARRIVE’,’packNwba’,’whMove’,’STOCKPLAN’,’jitreturn’,’TRANPLAN’,’TRANOUT’,’TRANIN’,’changeSku’,’PackBox’,’UNPackBox’,’otherout’,’owch’,’check’,’PICKDOWNFAST’,’RETURNRECEIPT’,’SALERETURN’,’PURCHASRETURN’)
and a.crdt >= ‘2020-11-01 00:00’ —开始时间
and a.crdt <= ‘2020-11-30 00:00’ —结束时间
AND a.inco IN (‘6924557300434’) —商品
AND w.whco IN (‘U01’) —仓库