| 问题现象： |
在进行未完成业务明细表查询时,某一时期的暂估数不能固定.比如说,5月份的暂估数,在进行到10月份时,在进行查询,发现5月份的暂估数已经发生变动.如何解决查询某一时期的固定暂估数.客户需要每月的新增暂估数,除未完成业务明细表以外,有没有其他的解决方法.(采购管理的暂估入库表是个滚动数,存货核算的暂估材料余额表没有入库单号和供应商,也不能解决) |
| 解决方案： |
可以参考下面的语句（不同的月份语句中的日期不同），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 |