beginTRANSACTIONkk-创建临时表CREATETABLE#fq(sCardIDint,PRIMARYkeynonCLUSTERED(sCardID)commitTRANSACTIONkk-初始化定义变量declareBegindatetime,enddatetime,iperiodint,acc_idvarchar(10),acc_yearvarchar(10)declareSQLnchar(2000)-得到当前本数据库的有关信息selectacc_id=999,acc_year=2013,iperiod=1,Begin=2013-01-0100:00:00.000,end=2013-01-3100:00:00.000修改成需要重算总账的相关信息-SELECTBegin=dBegin,end=dEndFROMUFSystem.dbo.UA_Period-wherecAcc_Id=acc_idandiYear=acc_yearandiId=iperiodSelectacc_id,acc_year,iperiod,Begin,end-取月初有效卡片序号数据，计算月初累计折旧数据ifiperiod12returninsert#fqSELECTmax(C.sCardID)FROMfa_CardsCWHERE(c.dInputDateBegin)AND(c.dTransDateBeginOrc.dTransDateIsNull)AND(c.dDisposeDateBeginOrc.dDisposeDateIsNull)groupbyC.sCardNum-SetSQL=updatefa_totalsetdblMonthDeprTotal=isnull(DT,0),dblMonthvalue=isnull(Dv,0)FROMfa_totalTleftjoin(SelectD.sDeptNum,C.sTypeNum,sum(D.dblValue)DV,sum(caseiperiod-1when0thendblDeprT1-dblDepr1when1thendblDeprT1when2thendblDeprT2when3thendblDeprT3when4thendblDeprT4when5thendblDeprT5when6thendblDeprT6when7thendblDeprT7when8thendblDeprT8when9thendblDeprT9when10thendblDeprT10when11thendblDeprT11else0end)DTfromfa_CardsCJOINfa_Cards_DetailDOND.sCardID=C.sCardIDANDJOINfa_DeprTransactions_DetailPONC.sCardNum=P.sCardNumD.sDeptNum=P.sDeptNumjoin#fqon#fq.sCardID=C.sCardIDWHEREC.dDisposeDateisnullandiyear=acc_yeargroupbyD.sDeptNum,C.sTypeNum)asAonT.sDeptNum=a.sDeptNumandT.sTypeNum=a.sTypeNumwhere(T.dblMonthDeprTotalisnull(DT,0)orT.dblMonthvalueisnull(Dv,0)andT.iperiod=iperiod-取月末有效卡片序号数据，计算月末累计折旧数据truncatetable#fqinsert#fqSELECTmax(C.sCardID)FROMfa_CardsCWHERE(c.dInputDate=end)AND(c.dTransDate=endOrc.dTransDateIsNull)AND(c.dDisposeDate=endOrc.dDisposeDateIsNull)groupbyC.sCardNum-SetSQL=updatefa_totalsetdblDeprTotal=isnull(DT,0),dblDepr=isnull(DP,0),dblvalue=isnull(Dv,0)FROMfa_totalTleftjoin(SelectD.sDeptNum,C.sTypeNum,sum(D.dblValue)DV,sum(caseiperiodwhen1thendblDeprT1when2thendblDeprT2when3thendblDeprT3when4thendblDeprT4when5thendblDeprT5when6thendblDeprT6when7thendblDeprT7when8thendblDeprT8when9thendblDeprT9when10thendblDeprT10when11thendblDeprT11when12thendblDeprT12else0end)DT,sum(caseiperiodwhen1thendblDepr1when2thendblDepr2when3thendblDepr3when4thendblDepr4when5thendblDepr5when6thendblDepr6when7thendblDepr7when8thendblDepr8when9thendblDepr9when10thendblDepr10when11thendblDepr11when12thendblDepr12else0end)DPfromfa_CardsCJOINfa_Cards_DetailDOND.sCardID=C.sCardIDJOINfa_DeprTransactions_DetailPONC.sCardNum=P.sCardNumANDD.sDeptNum=P.sDeptNumjoin#fqon#fq.sCardID=C.sCardIDWHEREC.dDisposeDateisnullandiyear=acc_yeargroupbyD.sDeptNum,C.sTypeNum)asAonT.sDeptNum=a.sDeptNumandT.sTypeNum=a.sTypeNumwhere(T.dblDeprTotalisnull(DT,0)ort.dblDeprisnull(DP,0)ort.dblvalueisnull(Dv,0)andT.iperiod=iperiodtruncatetable#fq-取本月新增或变动有效卡片序号数据，计算本月折旧变动情况insert#fqSELECTC.sCardIDFROMfa_CardsCWHERE(c.dInputDatebetweenbeginandend)or(c.dTransDatebetweenbeginandend)or(c.dDisposeDatebetweenbeginandend)updatefa_totalsetdblTransInDeprTotal=indt,dblTransOutDeprTotal=(outdt)FROMfa_totalTleftjoin(SelectD.sDeptNum,C.sTypeNum,sum(d.dblTransInDeprTCard)inDt,sum(d.dblTransOutDeprTCard+casewhenc.iopttype3then0elsecaseiperiodwhen1thenp.dblDepr1when2thenp.dblDepr2when3thenp.dblDepr3when4thenp.dblDepr4when5thenp.dblDepr5when6thenp.dblDepr6when7thenp.dblDepr7when8thenp.dblDepr8when9thenp.dblDepr9when10thenp.dblDepr10when11thenp.dblDepr11when12thenp.dblDepr12else0endend)outDTfromfa_CardsCJOINfa_Cards_DetailDOND.sCardID=C.sCardIDANDJOINfa_DeprTransactions_DetailPONC.sCardNum=P.sCardNumD.sDeptNum=P.sDeptNumjoin#fqon#fq.sCardID=C.sCardIDgroupbyD.sDeptNum,C.sTypeNum)asAonT.sDeptNum=a.sDeptNumandT.sTypeNum=a.sTypeNumwhereT.iperiod=iperiodand(dblTransOutDeprTotalisnull(outdt,0)ordblTransInDeprTotalisnull(indt,0)droptable#fq