一个非常有用的Sbo业务查询分析，利用Sbo单据事务日志表OINM，对指定期间的物料的期初、期末、入库、出库以及特殊类型的出库入库--在此我们分析了采购入库和采购退货两种特殊的业务类型--进行了">统计分析。
当然可以进行扩展，进而加上一些分析条件，比如对指定的仓库、指定的物料类型、排除指定的物料等条件进行约束。以下语句在某合作单位的生产环境上测试通过，并用于其生产分析。
_declare @dt1 smalldatetime, @dt2 smalldatetime, @ItemCode nvarchar(20) _select @dt1=t0.docDate FROM OINM t0 WHERE t0.createDate=[%0]_select @dt2=t1.docDate FROM OINM t1 WHERE t1.createDate=[%1]_select @ItemCode=t2.ItemCode from OITM t2 where t2.ItemCode = '[%2]' _select tc.ItmsGrpNam N'物料组名称',tc.ItmsGrpCod N'物料组',ta.itemcode N'物料号',tb.ItemName N'物料名称',sum(ta.begqty) N'期初数量',sum(ta.begtotal) N'期初金额', sum(Buyqty) N'采购入库数量',sum(Buytotal) N'采购入库金额',sum(Retqty) N'采购退货数量',sum(Rettotal) N'采购退货金额',sum(inqty) N'库存收货数量',sum(intotal) N'库存收货金额',sum(outqty) N'库存发货数量',sum(outtotal) N'库存发货金额',sum(endqty) N'期未数量',sum(endtotal) N'期未余额'from (_select ItemCode,sum(begqty) begqty,sum(begtotal) begtotal,sum(inqty)inqty,sum(intotal)intotal,sum(outqty) outqty,sum(outtotal) outtotal,sum(endqty) endqty,sum(endtotal) endtotal, sum(BuyQty) BuyQty, sum(BuyTotal) BuyTotal, sum(RetQty) RetQty, sum(RetTotal) RetTotal FROM ( _select a.ItemCode,sum(IsNull(IsNull(a.InQty,0),0)-IsNull(IsNull(a.OutQty,0),0)) begqty,sum(a.TransValue) begtotal,0 inqty,0 intotal,0 outqty,0 outtotal,0 endqty,0 endtotal, 0 BuyQty, 0 BuyTotal, 0 RetQty, 0 RetTotal from oinm a where (a.ItemCode=@ItemCode or IsNull(@ItemCode,'')='') and a.CreateDate < IsNull(@dt1, cast('2001-1-1' as smalldatetime)) group by a.ItemCode union all _select a.ItemCode,0,0,sum(IsNull(a.InQty,0)),sum(case when a.TransValue>=0 then a.TransValue else 0 end),sum(IsNull(a.OutQty,0)),sum(case when a.TransValue<0 then a.TransValue else 0 end),0,0,0,0,0,0 from oinm a where (a.ItemCode=@ItemCode or IsNull(@ItemCode,'')='') and a.CreateDate between IsNull(@dt1, cast('2001-1-1' as smalldatetime)) and IsNull(@dt2,getdate()) And a.TransType not in ('20','21') group by a.ItemCode union all _select a.ItemCode,0,0,0,0,0,0,sum(IsNull(IsNull(a.InQty,0),0)-IsNull(IsNull(a.OutQty,0),0)),sum(a.TransValue),0,0,0,0 from oinm a where (a.ItemCode=@ItemCode or IsNull(@ItemCode,'')='') and a.CreateDate<=IsNull(@dt2,getdate()) group by a.ItemCode union all _select a.ItemCode,0,0,0,0,0,0,0,0,sum(IsNull(IsNull(a.InQty,0),0)-IsNull(IsNull(a.OutQty,0),0)),sum(a.TransValue),0,0 from oinm a where (a.ItemCode=@ItemCode or IsNull(@ItemCode,'')='') and a.CreateDate between IsNull(@dt1, cast('2001-1-1' as smalldatetime)) and IsNull(@dt2,getdate()) and a.TransType='20' group by a.ItemCode union all _select a.ItemCode,0,0,0,0,0,0,0,0,0,0,sum(IsNull(IsNull(a.InQty,0),0)-IsNull(IsNull(a.OutQty,0),0)),sum(a.TransValue) from oinm a where (a.ItemCode=@ItemCode or IsNull(@ItemCode,'')='') and a.CreateDate between IsNull(@dt1, cast('2001-1-1' as smalldatetime)) and IsNull(@dt2,getdate()) and a.TransType='21' group by a.ItemCode ) tmp group by ItemCode) ta inner
join oitm tb on ta.itemcode = tb.ItemCode inner join oitb tc on tb.ItmsGrpCod = tc.ItmsGrpCodgroup by tc.ItmsGrpNam, tc.ItmsGrpCod, ta.itemcode, tb.ItemName
本文出自 “富盛软件” 博客，请务必保留此出处http://foresun.blog.51cto.com/221037/104533