1、SQL2005问题标题:问题描述:存货与总账对账不平的常见原因和处理思路分析。 问题分析:【原理说明】存货与总帐对帐,实际上就是一个按照科目抓取数据进行核对的过程,其中存货的数据来自于ia_subsidiary,总账的数据主要来自于Gl_accsum、gl_accvouch。其处理过程主要为:-存货期初数据抽取Select cInvHead,cCode_Name,cast(isnull(iAInPrice,0)-isnull(iAOutPrice,0) as decimal(38,2) as QcPrice,cast(isnull(iAInQuantity,0)-isnull(iAOutQua
2、ntity,0) as float) as QcQuantity,cast(0 as float) as InPRice,cast(0 as float) as InQuantity,cast(0 as float) as OutPrice,cast(0 as float) as outQuantity -into tempdb.OptVouchFJX0 from ia_subsidiary left join code on ia_subsidiary.cInvHead=code.ccode and code.iyear = 2011 where IA_subsidiary.iYear =
3、2011 And not cPZID is null and not cInvHead is null and ia_subsidiary.cvoutype N33and iMonth1Insert into tempdb.OptVouchFJX0 Select cDifHead,cCode_Name,cast(isnull(iDebitDifCost,0)-isnull(iCreditDifCost,0) as decimal(38,2) as QcPrice,cast(isnull(case when isnull(idebitdifcost,0)=0 then 0 else (cast(
4、isnull(iAInQuantity,0) as float)- cast(isnull(iAOutQuantity,0) as float) end),0)-isnull(case when brdflag=1 then (case when isnull(iCreditDifCost,0)=0 then 0 else (cast(isnull(iAInQuantity,0) as float)- cast(isnull(iAOutQuantity,0) as float) end) else (case when isnull(iCreditDifCost,0)=0 then 0 els
5、e (cast(isnull(iAoutQuantity,0) as float)- cast(isnull(iAinQuantity,0) as float) end) end) ,0) as float) as QcQuantity,cast(0 as float) as InPRice,cast(0 as float) as InQuantity,cast(0 as float) as OutPrice,cast(0 as float) as outQuantity left join code on ia_subsidiary.cDifHead=code.ccode and code.
6、iYear = 2011 and not cDifHead is null -存货日常发生数抽取cast(0 as float) as qcPRice,cast(0 as float) as qcQuantity,cast(isnull(iAInPrice,0) as decimal(38,2) as inPrice,cast(isnull(iAInQuantity,0) as float) as inQuantity,cast(isnull(iAOutPrice,0) as decimal(38,2) as OutPrice,cast(isnull(iAOutQuantity,0) as f
7、loat) as outQuantity and iMonth=1cast(0 as float) as QcPrice,0 as qcQuantity,isnull(iDebitDifCost,0) as InPrice,(case when isnull(idebitdifcost,0)=0 then 0 else (cast(isnull(iAInQuantity,0) as float)- cast(isnull(iAOutQuantity,0) as float) end) as inQuantity,cast(isnull(iCreditDifCost,0) as decimal(
8、38,2) as OutPrice,(case when brdflag=1 then (case when isnull(iCreditDifCost,0)=0 then 0 else (cast(isnull(iAInQuantity,0) as float)- cast(isnull(iAOutQuantity,0) as float) end) else (case when isnull(iCreditDifCost,0)=0 then 0 else (cast(isnull(iAoutQuantity,0) as float)- cast(isnull(iAinQuantity,0
9、) as float) end) end) as outQuantity left join code on ia_subsidiary.cDifHead=code.ccode and code.iyear = 2011 -按科目汇总存货期初和日常发生数据ltrim(str(cast(sum(cast(qcPrice as decimal(38,2) as decimal(38,2),20,2),ltrim(str(cast(sum(qcQuantity) as decimal(38,2),20,2),ltrim(str(cast(sum(cast(InPrice as decimal(38,
10、2) as decimal(38,2),20,2),ltrim(str(cast(sum(inQuantity) as decimal(38,2),20,2),ltrim(str(cast(sum(cast(OutPrice as decimal(38,2) as decimal(38,2),20,2),ltrim(str(cast(sum(OutQuantity) as decimal(38,2),20,2),ltrim(str(cast(sum(cast(qcPrice as decimal(38,2)+cast(InPRice as decimal(38,2)-cast(Outprice
11、 as decimal(38,2) as decimal(38,2),20,2),ltrim(str(cast(sum(qcquantity+inquantity-outquantity) as decimal(38,2),20,2) from tempdb.OptVouchFJX0 group by cInvHead,cCode_Name order by cInvHead-总账期初数据抽取-总账期初未记账数据抽取Select ccode,null,cast(sum(md)-sum(mc) as decimal(38,2),cast(sum(nd_s)-sum(nc_s) as decima
12、l(38,2),0,0,0,0 From GL_accVouch Where isnull(iflag,0) 1 And isNull(ibook,0) And ccode= N1403and (iyear=2011 And iperiod1) Or iyear2011) Group By cCode-总账期初已记账数据抽取cast(md-mc as decimal(38,2),cast(nd_s-nc_s as decimal(38,2),0,0,0,0 From Gl_accsum Where iperiod1 And Gl_accsum.ccode= Nand iyear=2011-总账日常未记账凭证数据抽取Select ccode,null,0,0,sum(md),sum(nd_s),sum(mc),sum(nc_s) where iperiod=1 And isnull(iflag,0) and iyear=2011 -总账日常已记账数据抽取Se
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1