报表分析

预算调整报表（YY_BudgetingAccountCompany_TZSH）：

--声明变量并赋值declare @CompanyID varchar(100),@Year varchar(5)set @CompanyID='6F1A959E-9EC1-4D2C-B5C7-D1B5C5C033C3'set @Year='2012'--定义两个临时表，表结构完全一样declare @TableSet table(CompName varchar(100),Code varchar(50),ParentCode varchar(50),AccountCode varchar(50),AccountName varchar(100),ApprovalNumber decimal(18,2),AdjustingReportNumber decimal(18,2),AdjustingApprovalNumber decimal(18,2),Remark varchar(500))declare @TableSet1 table(CompName varchar(100),Code varchar(50),ParentCode varchar(50),AccountCode varchar(50),AccountName varchar(100),ApprovalNumber decimal(18,2),AdjustingReportNumber decimal(18,2),AdjustingApprovalNumber decimal(18,2),Remark varchar(500))declare @CompanyType intselect @CompanyType = case when B.DictSort_Id='BMDWXZ' and B.DictNo ='1' then 1when B.DictSort_Id='BMDWXZ' and B.DictNo ='2' then 2when B.DictSort_Id='BMDWXZ' and B.DictNo ='3' then 3when B.DictSort_Id='BMDWXZ' and B.DictNo ='4' then 4else 1 end  from BM_SYSCompanyEXT A inner join EHS_SYS_DictInfo B on A.CompanyNature = B.DictInfo_Id  where A.ID = @CompanyIDif(@CompanyType=3 or @CompanyType=4)begininsert into @TableSet1   --通过CompanyID和预算周期等条件，找出预算科目拆分的信息select D.CompName,replace(B.AccountCode,H.CompanyCode,'') as Code,replace(G.AccountCode,H.CompanyCode,'') as ParentCode,E.CompanyCode + replace(B.AccountCode,H.CompanyCode,''),B.AccountName,convert(decimal(18,2),isnull(A.ResolutionApprovalNum,0)),convert(decimal(18,2),isnull(A.AdResolutionNum,0)),convert(decimal(18,2),isnull(A.AdResolutionApprovalNum,0)),null as Remarkfrom BM_AccountResolution A inner join BM_BudgetCycle C on C.ID = A.BudgetCycleIDinner join Org_Company D on A.CompanyID = D.IDinner join BM_CompanyBudgetAccounts B on A.StandardAccountId = B.IDinner join BM_SYSCompanyEXT E on D.ID = E.IDinner join EHS_SYS_DictInfo F on F.DictInfo_Id = B.BudgetTypeinner join BM_SYSCompanyEXT H on B.CompanyID = H.IDleft join BM_CompanyBudgetAccounts G on B.ParentAccountID = G.IDwhereB.Status=1and isnull(B.AccountType,0) <> 1and F.DictNo = 'YY' and F.DictSort_Id = 'BMYSLX'and E.ID = @CompanyIDand C.[Year] = @Yearendinsert into @TableSet  --通过CompanyID和预算周期等条件SELECT a.CompName,a.Code,a.ParentCode,a.AccountCode,a.AccountName,a.ApprovalNumber,a.AdjustingReportNumber,a.AdjustingApprovalNumber,b.opinion AS AdjustingRemarkFROM (select e.CompName,replace(a.AccountCode,f.CompanyCode,'') as Code,replace(g.AccountCode,f.CompanyCode,'') as ParentCode,a.AccountCode,a.AccountName,convert(decimal(18,2),b.ApprovalNumber) AS ApprovalNumber,convert(decimal(18,2),b.AdjustingReportNumber) AS AdjustingReportNumber,convert(decimal(18,2),b.AdjustingApprovalNumber) AS AdjustingApprovalNumber,b.AdjustingRemark,b.BudgetAccountIDfrom BM_CompanyBudgetAccounts a inner join BM_BudgetingAccount b on a.ID = b.BudgetAccountID inner join BM_BudgetingInvoice c on b.BudgetingInvoiceID = c.ID inner join BM_BudgetCycle d on c.BudgetCycleID = d.ID inner join org_company e on a.CompanyID = e.IDinner join BM_SYSCompanyEXT f on e.ID = f.IDinner join EHS_SYS_DictInfo h on h.DictInfo_Id = f.CompanyNatureleft join BM_CompanyBudgetAccounts g on a.ParentAccountID = g.IDwhere a.Status=1and isnull(a.AccountType,0) <> 1and f.ID = @CompanyID and d.[Year] = @Year) aLEFT JOIN (select j.AdjustmentSubjectID,j.opinion from (SELECT d.AdjustmentSubjectID,o.opinion,o.ApprovalDate FROM BM_BudgetAdjustmentInvoice hinner join BM_BudgetCycle a1 on a1.ID = h.BudgetCycleID inner join BM_BudgetAdjustmentAccount d on h.ID=d.AdjustmentInvoiceIDleft join AM_ApprovalOpinion o on o.BudgetingAccountID=d.IDwhere h.CompanyID=@CompanyID and a1.[Year]=@Year) AS j inner join (SELECT d.AdjustmentSubjectID,MAX(ApprovalDate) AS ApprovalDate FROM BM_BudgetAdjustmentInvoice hinner join BM_BudgetCycle a1 on a1.ID = h.BudgetCycleID inner join BM_BudgetAdjustmentAccount d on h.ID=d.AdjustmentInvoiceIDleft join AM_ApprovalOpinion o on o.BudgetingAccountID=d.IDwhere h.CompanyID=@CompanyID and a1.[Year]=@YearGROUP BY d.AdjustmentSubjectID) AS kon j.AdjustmentSubjectID=k.AdjustmentSubjectID and j.ApprovalDate=k.ApprovalDate) b on a.BudgetAccountID=b.AdjustmentSubjectIDselect A.CompName, --公司名称A.Code,     A.ParentCode, A.AccountCode,--科目编码A.AccountName,--科目名称isnull(A.ApprovalNumber,0)+isnull(B.ApprovalNumber,0) as ApprovalNumber,--编制审核数case when (isnull(A.AdjustingReportNumber,0)+isnull(B.AdjustingReportNumber,0)) -(isnull(A.ApprovalNumber,0)+isnull(B.ApprovalNumber,0)) = 0 then nullelse (isnull(A.AdjustingReportNumber,0)+isnull(B.AdjustingReportNumber,0)) -(isnull(A.ApprovalNumber,0)+isnull(B.ApprovalNumber,0)) end as AdNum,isnull(A.AdjustingReportNumber,0)+isnull(B.AdjustingReportNumber,0) as AdjustingReportNumber,--最新调整上报数case when (isnull(A.AdjustingApprovalNumber,0)+isnull(B.AdjustingApprovalNumber,0)) -(isnull(A.ApprovalNumber,0)+isnull(B.ApprovalNumber,0)) =0 then nullelse (isnull(A.AdjustingApprovalNumber,0)+isnull(B.AdjustingApprovalNumber,0)) -(isnull(A.ApprovalNumber,0)+isnull(B.ApprovalNumber,0)) end as AdApprovalNum,isnull(A.AdjustingApprovalNumber,0)+isnull(B.AdjustingApprovalNumber,0) as AdjustingApprovalNumber,--最新调整审核数A.Remark --备注from @TableSet A left join @TableSet1 B on A.Code = B.Code and isnull(A.ParentCode,'') = isnull(B.ParentCode,'')and A.AccountCode=B.AccountCode and A.AccountName = B.AccountName unionselect A.CompName,A.Code,A.ParentCode,A.AccountCode,A.AccountName,isnull(A.ApprovalNumber,0) as ApprovalNumber,case when isnull(A.AdjustingReportNumber,0) - isnull(A.ApprovalNumber,0) =0 then null else isnull(A.AdjustingReportNumber,0) - isnull(A.ApprovalNumber,0) end as AdNum,isnull(A.AdjustingReportNumber,0) as AdjustingReportNumber,case when isnull(A.AdjustingApprovalNumber,0) - isnull(A.ApprovalNumber,0)=0 then null else isnull(A.AdjustingApprovalNumber,0) - isnull(A.ApprovalNumber,0) end as AdApprovalNum,isnull(A.AdjustingApprovalNumber,0) as AdjustingApprovalNumber,A.Remark from @TableSet1 Awhere not exists(select B.Code from @TableSet B where A.Code = B.Code and isnull(A.ParentCode,'') = isnull(B.ParentCode,'')and A.AccountCode=B.AccountCode and A.AccountName = B.AccountName)order by A.Code

关于湛江0.01的误差：主要是有预算科目拆分值和预算科目值的不对应造成的：

获取拆分的ID：

select A.*,D.CompName,replace(B.AccountCode,H.CompanyCode,'') as Code,replace(G.AccountCode,H.CompanyCode,'') as ParentCode,E.CompanyCode + replace(B.AccountCode,H.CompanyCode,''),B.AccountName,convert(decimal(18,2),isnull(A.ResolutionApprovalNum,0)),convert(decimal(18,2),isnull(A.AdResolutionNum,0)),convert(decimal(18,2),isnull(A.AdResolutionApprovalNum,0)),null as Remarkfrom BM_AccountResolution A inner join BM_BudgetCycle C on C.ID = A.BudgetCycleIDinner join Org_Company D on A.CompanyID = D.IDinner join BM_CompanyBudgetAccounts B on A.StandardAccountId = B.IDinner join BM_SYSCompanyEXT E on D.ID = E.IDinner join EHS_SYS_DictInfo F on F.DictInfo_Id = B.BudgetTypeinner join BM_SYSCompanyEXT H on B.CompanyID = H.IDleft join BM_CompanyBudgetAccounts G on B.ParentAccountID = G.IDwhereB.Status=1and isnull(B.AccountType,0) <> 1and F.DictNo = 'YY' and F.DictSort_Id = 'BMYSLX'and E.ID ='6F1A959E-9EC1-4D2C-B5C7-D1B5C5C033C3'and C.[Year] ='2012'

A.*找到拆分ID，进行更新

SELECT * from BM_AccountResolution where ID='487c4a43-5a4d-4bb5-aa56-20a071f6330b'

UPDATE BM_AccountResolution  SET  AdResolutionApprovalNum =-547.8670 where ID='487c4a43-5a4d-4bb5-aa56-20a071f6330b' 

select * from  EHS_SYS_DictInfo  where dictSort_id='BMDWXZ' -- 预算单位性质
select * from EHS_sys_dictInfo where dictno='YY'  --营运单位预算
select * from EHS_sys_dictinfo where dictno='YH'  --养护预算
select * from eHS_sys_dictinfo where dictno='XM'  --建设项目单位预算