用友U8 在进行未完成业务明细表查询时,某一时期的暂估数不能固定.比如说,5月份的暂估数,在进行到10月份时,在进行查询,发现5月份的暂估数已经发生变动.如何解决查询某一时期的固定暂估数.客户需要每月的新增暂估数,除未完成业务明细表以外,有没有其他的解决方法.(采购管理的暂估入库表是个滚动数,存货核算的暂估材料余额表没有入库单号和供应商,也不能解决)

用友U8 在进行未完成业务明细表查询时,某一时期的暂估数不能固定.比如说,5月份的暂估数,在进行到10月份时,在进行查询,发现5月份的暂估数已经发生变动.如何解决查询某一时期的固定暂估数.客户需要每月的新增暂估数,除未完成业务明细表以外,有没有其他的解决方法.(采购管理的暂估入库表是个滚动数,存货核算的暂估材料余额表没有入库单号和供应商,也不能解决) 问题原因:由于未完成业务表只是根据入库单所在的表rdrecord(s)来统计，而该表中只记载总的累计结算数，所以暂估数会随着累计结算数的变化而变化。采购中的暂估入库余额表可以统计出来一定时期的暂估数，但只能按照供应商进行统计，如果想要显示出入库单号，则只能用自定义报表，可以考虑二次开发，取得界面上的日期加以统计，如果不二次开发，则需要每个月都作一张自定义报表 解决方法:可以参考下面的语句（不同的月份语句中的日期不同），5月份的暂估数： SELECT T1.cVenCode,T1.cInvCode,t1.ccode, (ISNULL(T1.iQuantity,0)-ISNULL(T2.iSQuantity,0)+ISNULL(T3.iSVQuantity,0)) AS 未结数量, (ISNULL(T1.iAPrice,0)-ISNULL(T2.iMoney,0)+ISNULL(T3.iSVAPrice,0)) AS 未结暂估金额,ISNULL(T1.iQuantity,0) as 入库数量 From (SELECT RdRecord.cVenCode,RdRecords.cInvCode,RdRecords.AutoID,RdRecords.iQuantity, RdRecords.fACost,RdRecords.iAPrice,rdrecord.ccode FROM RdRecord INNER JOIN RdRecords ON RdRecord.ID=RdRecords.ID LEFT JOIN Inventory ON RdRecords.cInvCode=Inventory.cInvCode LEFT JOIN Vendor ON RdRecord.cVenCode=Vendor.cVenCode WHERE cVouchType='01' AND cBusType='普通采购' AND RdRecord.dDate>='2005-5-01' AND RdRecord.dDate<'2005-6-1' UNION SELECT RdRecord.cVenCode,RdRecords.cInvCode,RdRecords.AutoID,RdRecords.iQuantity, RdRecords.fACost,RdRecords.iAPrice,rdrecord.ccode FROM RdRecord INNER JOIN RdRecords ON RdRecord.ID=RdRecords.ID LEFT JOIN Inventory ON RdRecords.cInvCode=Inventory.cInvCode LEFT JOIN Vendor ON RdRecord.cVenCode=Vendor.cVenCode WHERE (RdRecord.bPuFirst=1) AND cBusType='普通采购' ) AS T1 Left Join (SELECT RdRecord.cVenCode,RdRecords.cInvCode,RdRecords.AutoID,RdRecords.iSQuantity, RdRecords.iMoney FROM RdRecord INNER JOIN RdRecords ON RdRecord.ID=RdRecords.ID LEFT JOIN Inventory ON RdRecords.cInvCode=Inventory.cInvCode LEFT JOIN Vendor ON RdRecord.cVenCode=Vendor.cVenCode WHERE 1=1 AND cBusType='普通采购' ) AS T2 ON T1.AutoID=T2.AutoID Left Join (SELECT PurSettleVouchs.iRdsID,SUM(PurSettleVouchs.iSVQuantity) AS iSVQuantity, SUM(PurSettleVouchs.iSVAPrice) AS iSVAPrice FROM PurSettleVouch INNER JOIN PurSettleVouchs ON PurSettleVouch.PSVID=PurSettleVouchs.PSVID WHERE PurSettleVouch.dSVDate>='2005-6-1' AND cBusType='普通采购' GROUP BY PurSettleVouchs.iRdsID) as T3 ON T1.AutoID=T3.iRdsID
解决方案：
问题原因:由于未完成业务表只是根据入库单所在的表rdrecord(s)来统计，而该表中只记载总的累计结算数，所以暂估数会随着累计结算数的变化而变化。采购中的暂估入库余额表可以统计出来一定时期的暂估数，但只能按照供应商进行统计，如果想要显示出入库单号，则只能用自定义报表，可以考虑二次开发，取得界面上的日期加以统计，如果不二次开发，则需要每个月都作一张自定义报表 解决方法:可以参考下面的语句（不同的月份语句中的日期不同），5月份的暂估数： SELECT T1.cVenCode,T1.cInvCode,t1.ccode, (ISNULL(T1.iQuantity,0)-ISNULL(T2.iSQuantity,0)+ISNULL(T3.iSVQuantity,0)) AS 未结数量, (ISNULL(T1.iAPrice,0)-ISNULL(T2.iMoney,0)+ISNULL(T3.iSVAPrice,0)) AS 未结暂估金额,ISNULL(T1.iQuantity,0) as 入库数量 From (SELECT RdRecord.cVenCode,RdRecords.cInvCode,RdRecords.AutoID,RdRecords.iQuantity, RdRecords.fACost,RdRecords.iAPrice,rdrecord.ccode FROM RdRecord INNER JOIN RdRecords ON RdRecord.ID=RdRecords.ID LEFT JOIN Inventory ON RdRecords.cInvCode=Inventory.cInvCode LEFT JOIN Vendor ON RdRecord.cVenCode=Vendor.cVenCode WHERE cVouchType='01' AND cBusType='普通采购' AND RdRecord.dDate>='2005-5-01' AND RdRecord.dDate<'2005-6-1' UNION SELECT RdRecord.cVenCode,RdRecords.cInvCode,RdRecords.AutoID,RdRecords.iQuantity, RdRecords.fACost,RdRecords.iAPrice,rdrecord.ccode FROM RdRecord INNER JOIN RdRecords ON RdRecord.ID=RdRecords.ID LEFT JOIN Inventory ON RdRecords.cInvCode=Inventory.cInvCode LEFT JOIN Vendor ON RdRecord.cVenCode=Vendor.cVenCode WHERE (RdRecord.bPuFirst=1) AND cBusType='普通采购' ) AS T1 Left Join (SELECT RdRecord.cVenCode,RdRecords.cInvCode,RdRecords.AutoID,RdRecords.iSQuantity, RdRecords.iMoney FROM RdRecord INNER JOIN RdRecords ON RdRecord.ID=RdRecords.ID LEFT JOIN Inventory ON RdRecords.cInvCode=Inventory.cInvCode LEFT JOIN Vendor ON RdRecord.cVenCode=Vendor.cVenCode WHERE 1=1 AND cBusType='普通采购' ) AS T2 ON T1.AutoID=T2.AutoID Left Join (SELECT PurSettleVouchs.iRdsID,SUM(PurSettleVouchs.iSVQuantity) AS iSVQuantity, SUM(PurSettleVouchs.iSVAPrice) AS iSVAPrice FROM PurSettleVouch INNER JOIN PurSettleVouchs ON PurSettleVouch.PSVID=PurSettleVouchs.PSVID WHERE PurSettleVouch.dSVDate>='2005-6-1' AND cBusType='普通采购' GROUP BY PurSettleVouchs.iRdsID) as T3 ON T1.AutoID=T3.iRdsID