查入库数量与采购数量 有差异的记录

use GKSERP
go

--采购主档
SELECT TOP 9 * FROM MAT_CG
WHERE PNO='CC080601239'
--采购明细
SELECT TOP 9 * FROM MAT_CG_C
WHERE PNO='cb080700285'

--查入库数量与采购数量有差异的记录,正数表示超交,负数表示久交.stock_no:仓库号(16:是暂收仓).
-- 注意: 暂收仓和采购单为空的入库单都不进行统计.
select a.*,pur.cg_qty, (a.sumQTY-PUR.cg_qty) as Variance from (
select matno,pno_tmp,unit_tmp, sum(ys_qtySum) as sumQTY
from mat_ysmx_c
where pno_tmp is not null and stock_no not in('16')
group by matno,pno_tmp,unit_tmp ) a
, ( select pno,matno,sum(CG_QTY) AS CG_QTY from mat_cg_c GRoup by pno,matno ) pur
where a.pno_tmp=pur.pno and a.matno=pur.matno
and (a.sumQTY-PUR.cg_qty)<>0.000 and a.pno_tmp ='cb080700285' --'CC080601187'


-- order by a.pno_tmp

--入库明细总数量:42962 (采购单为非空)
select count(*) from mat_ysmx_c where pno_tmp is not null

--入库明细总数量:4515 (采购单为空)
select count(*) from mat_ysmx_c where pno_tmp is null
--
select matno,pno_tmp,unit_tmp, sum(ys_qtySum) as sumQTY
from mat_ysmx_c
-- where pno_tmp ='CC080601187'
where pno_tmp is not null --where stock_no not in('16')
group by matno,pno_tmp,unit_tmp /*order by pno_tmp */

--暂收仓 16条.
select count(*) from mat_ysmx_c where stock_no in('16')

--采购单空物料编号的记录
select pno,matno,sum(CG_QTY) AS CG_QTY from mat_cg_c
where pno is null or matno is null
GRoup by pno,matno

--采购单里物料编号为空的而又有入库的采购单记录
select pno,matno AS CG_QTY from mat_cg_c
where pno in (
select distinct pno_tmp from mat_ysmx_c )
and mat_cg_c.matno is null

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/729024/viewspace-564847/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/729024/viewspace-564847/