收发存汇总表的日期问题用友软件股份有限公司姓名：牛春艳2010年06月22日错误现象分析过程总结客户问题：890,未打任何补丁。客户录入一张2010年6月5日的采购入库单,未记账;在存货核算的收发存汇总表中查询时,过滤条件选择日期为2010-6-5至2010-6-5时,包含未记账单据,但是过滤后,却没有包含该笔入库数据。而过滤条件中选择日期为2010-6-5至2010-6-9时,包含未记账单据,却能过滤出该笔数据。客户咨询原因?问题现象问题现象首先,过滤条件中的“日期”，具体是指什么日期？对于已记账单据，“日期”是指记账日期；那么，对于未记账的单据，“日期”是指什么日期呢，是不是单据日期呢？客户就是这样认为的。分析过程通过事件探查器进行跟踪分析：分析过程收发存汇总表查询时，具体执行语句如下：insertintotempdb..[InOutSumTmpNCY0]select6asi,cast(INVENTORY.cInvCCodeasnvarchar(12))asGrade,rdrecords.cInvCode,inventory.cInvAddCode,inventory.cInvName,inventory.cInvStd,ComputationUnit.CComUnitName,iInvWeight,0ASQcQuantity,0ASQcPrice,0asqcDif,(casebRdFlagwhen1then(casewhen(rdrecords.iQuantityisnull)then0elserdrecords.iQuantityend)else0end)asInQuantity,(casebRdFlagwhen1then(casewhenwarehouse.cwhvaluestyle=N'计划价法'orwarehouse.cwhvaluestyle=N'售价法'thenInvCheckFreeIA870.finvrcost*RdRecords.iQuantityelse(casewhen(iPriceisnull)then0elseiPriceend)end)else0end)asInPrice,(casebRdFlagwhen1then(casewhenwarehouse.cwhvaluestyle=N'计划价法'orwarehouse.cwhvaluestyle=N'售价法'then((rdrecords.iprice-InvCheckFreeIA870.finvrcost*RdRecords.iQuantity))else0end)else0end)asInDif,(casebRdFlagwhen0then(casewhen(rdrecords.iQuantityisnull)then0elserdrecords.iQuantityend)else0end)asOutQuantity,(casebRdFlagwhen0then(casewhenwarehouse.cwhvaluestyle=N'计划价法'orwarehouse.cwhvaluestyle=N'售价法'thenInvCheckFreeIA870.finvrcost*RdRecords.iQuantityelse(casewhen(iPriceisnull)then0elseiPriceend)end)else0end)AsOutPrice,(casebRdFlagwhen0then(casewhenwarehouse.cwhvaluestyle=N'计划价法'orwarehouse.cwhvaluestyle=N'售价法'then((rdrecords.iprice-InvCheckFreeIA870.finvrcost*RdRecords.iQuantity))else0end)else0end)asOutDiffromrdrecordwith(nolock)leftjoinrdrecordswith(nolock)onrdrecord.ID=rdrecords.IDleftjoinInventorywith(nolock)onrdrecords.cInvCode=Inventory.cInvCodeleftjoinComputationUnitwith(nolock)ONinventory.cComunitCode=ComputationUnit.ccomunitcodeleftjoinwarehousewith(nolock)onrdrecord.cWhCode=warehouse.cwhcodeLeftjoinInvCheckFreeIA870with(nolock)onInvCheckFreeIA870.cinvcode=rdrecords.cinvcodeand((Isnull(InvCheckFreeIA870.cFree1,N'')=isnull(rdrecords.cfree1,N'')andInventory.bCheckFree1=1)orInventory.bCheckFree1=0)and((Isnull(InvCheckFreeIA870.cFree2,N'')=isnull(rdrecords.cfree2,N'')andInventory.bCheckFree2=1)orInventory.bCheckFree2=0)and((Isnull(InvCheckFreeIA870.cFree3,N'')=isnull(rdrecords.cfree3,N'')andInventory.bCheckFree3=1)orInventory.bCheckFree3=0)and((Isnull(InvCheckFreeIA870.cFree4,N'')=isnull(rdrecords.cfree4,N'')andInventory.bCheckFree4=1)orInventory.bCheckFree4=0)and((Isnull(InvCheckFreeIA870.cFree5,N'')=isnull(rdrecords.cfree5,N'')andInventory.bCheckFree5=1)orInventory.bCheckFree5=0)and((Isnull(InvCheckFreeIA870.cFree6,N'')=isnull(rdrecords.cfree6,N'')andInventory.bCheckFree6=1)orInventory.bCheckFree6=0)and((Isnull(InvCheckFreeIA870.cFree7,N'')=isnull(rdrecords.cfree7,N'')andInventory.bCheckFree7=1)orInventory.bCheckFree7=0)and((Isnull(InvCheckFreeIA870.cFree8,N'')=isnull(rdrecords.cfree8,N'')andInventory.bCheckFree8=1)orInventory.bCheckFree8=0)and((Isnull(InvCheckFreeIA870.cFree9,N'')=isnull(rdrecords.cfree9,N'')andInventory.bCheckFree9=1)orInventory.bCheckFree9=0)and((Isnull(InvCheckFreeIA870.cFree10,N'')=isnull(rdrecords.cfree10,N'')andInventory.bCheckFree10=1)orInventory.bCheckFree10=0)Where((isnull(RdRecords.cbAccounter,'')='')andisnull(rdrecords.bcosting,0)<>0andrdrecord.CBusType<>'预留入库'andrdrecord.CBusType<>'预留出库'andRdRecord.cVouchType<>N'33'Andnot(RdRecord.cVouchType=N'32')AndRdRecord.cVouchType<>N'34'AndNOTInventory.bService=1andrdrecords.cInvCode>=N'01001'Andrdrecords.cInvCode<=N'01001'And(Notrdrecord.cWhCodeisnull)Andisnull(Inventory.dEDate,N'9999-12-31')>'2010-06-05'andInventory.dSDate<='2010-06-05'AND(warehouse.bInCost=1orwarehouse.cwhcodeisnull)And(rdrecord.dDate>=N'2010-06-05'Andrdrecord.dDate<=N'2010-06-05')and(rdrecord.ddate>=N'2010-06-01'orrdrecord.bIAfirst=1))分析过程涉及到过滤条件的内容：Where((isnull(RdRecords.cbAccounter,'')='')andisnull(rdrecords.bcosting,0)<>0andrdrecord.CBusType<>'预留入库'andrdrecord.CBusType<>'预留出库'andRdRecord.cVouchType<>N'33'Andnot(RdRecord.cVouchType=N'32')AndRdRecord.cVouchType<>N'34'AndNOTInventory.bService=1andrdrecords.cInvCode>=N'01001'Andrdrecords.cInvCode<=N'01001'And(Notrdrecord.cWhCodeisnull)Andisnull(Inventory.dEDate,N'9999-12-31')>'2010-06-05'andInventory.dSDate<='2010-06-05'AND(warehouse.bInCost=1orwarehouse.cwhcodeisnull)And(rdrecord.dDate>=N'2010-06-05'Andrdrecord.dDate<=N'2010-06-05')and(rdrecord.ddate>=N'2010-06-01'orrdrecord.bIAfirst=1))经分析，发现过滤未记账的记录时，过滤条件中的日期，首先要求：1、存货档案的停用日期大于起始日期（或没有停用日期），存货的启用日期小于等于截止日期；2、而且，要求单据日期也要在过滤条件的日期范围之内。3、这两者是并且的关系。分析过程由于在客户问题中，单据日期没有问题，在日期范围之内；那么，我们去查看存货档案的停用日期、启用日期：分析过程结果发现，该存货没有停用；而启用日期却是2010-6-9，因此，这就是为什么日期范围在2010-6-5至2010-6-5之间时，该笔未记账的采购入库单据没有过滤出来的原因。分析过程收发存汇总表过滤条件的日期范围：1、对于已记账单据，是指记账日期；2、对于未记账单据，该日期范围不仅是指单据日期；而且也与存货的停用日期、启用日期有关。只有在日期范围之内启用，并且停用日期大于起始日期（或没有停用）的存货才能过滤出来。总结
