| 解决方案： |
在2006年度的数据库里一定要按顺序执行
第一步：
if exists (select * from sysobjects where id = object_id(N'[dbo].[aatjb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aatjb]
GO
CREATE TABLE [dbo].[aatjb] (
[Num1] [varchar] (48) NULL ,
[sDeptName] [varchar] (48) NULL ,
[sTypeName] [varchar] (30) NULL ,
[iperiod] [int] NULL ,
[lCount] [int] NULL ,
[Life] [varchar] (12) NULL ,
[sUnit] [varchar] (10) NULL ,
[dblBalanceValue] [float] NULL ,
[dblDeprT] [float] NULL ,
[dblBalance] [float] NULL ,
[dblRate] [float] NULL ,
[dblBVOfSum] [float] NULL ,
[dblDepr] [float] NULL ,
[dblCanWorkOfSum] [float] NULL ,
[dblWorkT] [float] NULL ,
[dblBuildAreaOfSum] [float] NULL ,
[lBuildNumOfSum] [int] NULL ,
[lMachinNumOfSum] [int] NULL ,
[dblMachinWOfSum] [float] NULL
) ON [PRIMARY]
GO
第二步：
if exists (select * from sysobjects where id = object_id(N'[dbo].[fa_Q_TJB_new]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[fa_Q_TJB_new]
GO
SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON
GO
Create PROC fa_Q_TJB_new(@iDeptLevel int, @iTypeLevel int, @LoginPeriod int, @LoginDate DateTime, @lDeptChilds int, @lTypeChilds int) AS
---------------------- Create YXKP Sets ----------------------------
CREATE TABLE #YXKP(sCardID varchar(20))
EXEC fa_Q_INSERT_YXKP @LoginDate
---------------------- Cretae TempTable ----------------------------
SELECT '2' AS sSort, '' AS Num1, '' AS Num2, Count(*) AS lCount,
Sum(fa_Cards.dblValue) AS dblBalanceValue,
----dblDeprT(累计折旧)----
Sum((CASE @LoginPeriod WHEN 1 THEN [dblDeprT1] WHEN 2 THEN [dblDeprT2] WHEN 3 THEN [dblDeprT3] WHEN 4 THEN [dblDeprT4] WHEN 5 THEN [dblDeprT5] WHEN 6 THEN [dblDeprT6] WHEN 7 THEN [dblDeprT7] WHEN 8 THEN [dblDeprT8] WHEN 9 THEN [dblDeprT9] WHEN 10 THEN [dblDeprT10] WHEN 11 THEN [dblDeprT11] WHEN 12 THEN [dblDeprT12] END)) AS dblDeprT,
Sum(fa_Cards.dblBV) AS dblBVOfSum,
----dblDepr(本期折旧)----
Sum((CASE @LoginPeriod WHEN 1 THEN [dblDepr1] WHEN 2 THEN [dblDepr2] WHEN 3 THEN [dblDepr3] WHEN 4 THEN [dblDepr4] WHEN 5 THEN [dblDepr5] WHEN 6 THEN [dblDepr6] WHEN 7 THEN [dblDepr7] WHEN 8 THEN [dblDepr8] WHEN 9 THEN [dblDepr9] WHEN 10 THEN [dblDepr10] WHEN 11 THEN [dblDepr11] ELSE [dblDepr12] END)) AS dblDepr,
Sum(fa_Cards.dblCanWork) AS dblCanWorkOfSum,
----dblWorkT(累计工作量)----
Sum((CASE @LoginPeriod WHEN 1 THEN [dblWorkT1] WHEN 2 THEN [dblWorkT2] WHEN 3 THEN [dblWorkT3] WHEN 4 THEN [dblWorkT4] WHEN 5 THEN [dblWorkT5] WHEN 6 THEN [dblWorkT6] WHEN 7 THEN [dblWorkT7] WHEN 8 THEN [dblWorkT8] WHEN 9 THEN [dblWorkT9] WHEN 10 THEN [dblWorkT10] WHEN 11 THEN [dblWorkT11] ELSE [dblWorkT12] END)) AS dblWorkT,
Sum(fa_Cards.dblBuildArea) AS dblBuildAreaOfSum,
Sum(fa_Cards.lBuildNum) AS lBuildNumOfSum,
Sum(fa_Cards.lMachinNum) AS lMachinNumOfSum,
Sum(fa_Cards.dblMachinW) AS dblMachinWOfSum
INTO #fa_Q_TJB_Pre1
FROM ((((fa_Cards INNER JOIN fa_DeprTransactions ON fa_Cards.sCardNum = fa_DeprTransactions.sCardNum) LEFT JOIN fa_WorkLoad ON fa_Cards.sCardNum = fa_WorkLoad.sCardNum) INNER JOIN fa_Departments ON fa_Cards.sDeptNum = fa_Departments.sNum) INNER JOIN fa_AssetTypes ON fa_Cards.sTypeNum = fa_AssetTypes.sNum) INNER JOIN #YXKP ON fa_Cards.sCardID = #YXKP.sCardID
WHERE (((fa_Cards.dDisposeDate) Is Null) AND ((fa_Departments.iLevel)>=@iDeptLevel) AND ((fa_AssetTypes.iLevel)>=@iTypeLevel)) OR (((fa_Cards.dDisposeDate) Is Null) AND ((fa_AssetTypes.iLevel)>=@iTypeLevel) AND ((fa_Departments.lChilds)=@lDeptChilds)) OR (((fa_Cards.dDisposeDate) Is Null) AND ((fa_Departments.iLevel)>=@iDeptLevel) AND ((fa_AssetTypes.lChilds)=@lTypeChilds)) OR (((fa_Cards.dDisposeDate) Is Null) AND ((fa_Departments.lChilds)=@lDeptChilds) AND ((fa_AssetTypes.lChilds)=@lTypeChilds))
UNION
SELECT '1' AS sSort,
(CASE WHEN @iDeptLevel=1 THEN [fa_Departments].[sNum1] ELSE (CASE WHEN @iDeptLevel=2 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] ELSE (CASE WHEN @iDeptLevel=3 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] + [fa_Departments].[sNum3] ELSE (CASE WHEN @iDeptLevel=4 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] + [fa_Departments].[sNum3] + [fa_Departments].[sNum4] ELSE [fa_Departments].[sNum] END) END) END) END) AS Num1,
'' AS Num2, Count(*) AS lCount,
Sum(fa_Cards.dblValue) AS dblBalanceValue,
----dblDeprT(累计折旧)----
Sum((CASE @LoginPeriod WHEN 1 THEN [dblDeprT1] WHEN 2 THEN [dblDeprT2] WHEN 3 THEN [dblDeprT3] WHEN 4 THEN [dblDeprT4] WHEN 5 THEN [dblDeprT5] WHEN 6 THEN [dblDeprT6] WHEN 7 THEN [dblDeprT7] WHEN 8 THEN [dblDeprT8] WHEN 9 THEN [dblDeprT9] WHEN 10 THEN [dblDeprT10] WHEN 11 THEN [dblDeprT11] WHEN 12 THEN [dblDeprT12] END)) AS dblDeprT,
Sum(fa_Cards.dblBV) AS dblBVOfSum,
----dblDepr(本期折旧)----
Sum((CASE @LoginPeriod WHEN 1 THEN [dblDepr1] WHEN 2 THEN [dblDepr2] WHEN 3 THEN [dblDepr3] WHEN 4 THEN [dblDepr4] WHEN 5 THEN [dblDepr5] WHEN 6 THEN [dblDepr6] WHEN 7 THEN [dblDepr7] WHEN 8 THEN [dblDepr8] WHEN 9 THEN [dblDepr9] WHEN 10 THEN [dblDepr10] WHEN 11 THEN [dblDepr11] ELSE [dblDepr12] END)) AS dblDepr,
Sum(fa_Cards.dblCanWork) AS dblCanWorkOfSum,
----dblWorkT(累计工作量)----
Sum((CASE @LoginPeriod WHEN 1 THEN [dblWorkT1] WHEN 2 THEN [dblWorkT2] WHEN 3 THEN [dblWorkT3] WHEN 4 THEN [dblWorkT4] WHEN 5 THEN [dblWorkT5] WHEN 6 THEN [dblWorkT6] WHEN 7 THEN [dblWorkT7] WHEN 8 THEN [dblWorkT8] WHEN 9 THEN [dblWorkT9] WHEN 10 THEN [dblWorkT10] WHEN 11 THEN [dblWorkT11] ELSE [dblWorkT12] END)) AS dblWorkT,
Sum(fa_Cards.dblBuildArea) AS dblBuildAreaOfSum,
Sum(fa_Cards.lBuildNum) AS lBuildNumOfSum,
Sum(fa_Cards.lMachinNum) AS lMachinNumOfSum,
Sum(fa_Cards.dblMachinW) AS dblMachinWOfSum
FROM ((((fa_Cards INNER JOIN fa_DeprTransactions ON fa_Cards.sCardNum = fa_DeprTransactions.sCardNum) LEFT JOIN fa_WorkLoad ON fa_Cards.sCardNum = fa_WorkLoad.sCardNum) INNER JOIN fa_Departments ON fa_Cards.sDeptNum = fa_Departments.sNum) INNER JOIN fa_AssetTypes ON fa_Cards.sTypeNum = fa_AssetTypes.sNum) INNER JOIN #YXKP ON fa_Cards.sCardID = #YXKP.sCardID
WHERE (((fa_Cards.dDisposeDate) Is Null) AND ((fa_Departments.iLevel)>=@iDeptLevel) AND ((fa_AssetTypes.iLevel)>=@iTypeLevel)) OR (((fa_Cards.dDisposeDate) Is Null) AND ((fa_AssetTypes.iLevel)>=@iTypeLevel) AND ((fa_Departments.lChilds)=@lDeptChilds)) OR (((fa_Cards.dDisposeDate) Is Null) AND ((fa_Departments.iLevel)>=@iDeptLevel) AND ((fa_AssetTypes.lChilds)=@lTypeChilds)) OR (((fa_Cards.dDisposeDate) Is Null) AND ((fa_Departments.lChilds)=@lDeptChilds) AND ((fa_AssetTypes.lChilds)=@lTypeChilds))
GROUP BY (CASE WHEN @iDeptLevel=1 THEN [fa_Departments].[sNum1] ELSE (CASE WHEN @iDeptLevel=2 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] ELSE (CASE WHEN @iDeptLevel=3 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] + [fa_Departments].[sNum3] ELSE (CASE WHEN @iDeptLevel=4 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] + [fa_Departments].[sNum3] + [fa_Departments].[sNum4] ELSE [fa_Departments].[sNum] END) END) END) END)
UNION
SELECT '1' AS sSort, (CASE WHEN @iDeptLevel=1 THEN [fa_Departments].[sNum1] ELSE (CASE WHEN @iDeptLevel=2 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] ELSE (CASE WHEN @iDeptLevel=3 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] + [fa_Departments].[sNum3] ELSE (CASE WHEN @iDeptLevel=4 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] + [fa_Departments].[sNum3] + [fa_Departments].[sNum4] ELSE [fa_Departments].[sNum] END) END) END) END) AS Num1,
(CASE WHEN @iTypeLevel=1 THEN [fa_AssetTypes].[sNum1] ELSE (CASE WHEN @iTypeLevel=2 THEN [fa_AssetTypes].[sNum1] + [fa_AssetTypes].[sNum2] ELSE (CASE WHEN @iTypeLevel=3 THEN [fa_AssetTypes].[sNum1] + [fa_AssetTypes].[sNum2] + [fa_AssetTypes].[sNum3] ELSE [fa_AssetTypes].[sNum] END) END) END) AS Num2,
Count(*) AS lCount,
Sum(fa_Cards.dblValue) AS dblBalanceValue,
----dblDeprT(累计折旧)----
Sum((CASE @LoginPeriod WHEN 1 THEN [dblDeprT1] WHEN 2 THEN [dblDeprT2] WHEN 3 THEN [dblDeprT3] WHEN 4 THEN [dblDeprT4] WHEN 5 THEN [dblDeprT5] WHEN 6 THEN [dblDeprT6] WHEN 7 THEN [dblDeprT7] WHEN 8 THEN [dblDeprT8] WHEN 9 THEN [dblDeprT9] WHEN 10 THEN [dblDeprT10] WHEN 11 THEN [dblDeprT11] WHEN 12 THEN [dblDeprT12] END)) AS dblDeprT,
Sum(fa_Cards.dblBV) AS dblBVOfSum,
----dblDepr(本期折旧)----
Sum((CASE @LoginPeriod WHEN 1 THEN [dblDepr1] WHEN 2 THEN [dblDepr2] WHEN 3 THEN [dblDepr3] WHEN 4 THEN [dblDepr4] WHEN 5 THEN [dblDepr5] WHEN 6 THEN [dblDepr6] WHEN 7 THEN [dblDepr7] WHEN 8 THEN [dblDepr8] WHEN 9 THEN [dblDepr9] WHEN 10 THEN [dblDepr10] WHEN 11 THEN [dblDepr11] ELSE [dblDepr12] END)) AS dblDepr,
Sum(fa_Cards.dblCanWork) AS dblCanWorkOfSum,
----dblWorkT(累计工作量)----
Sum((CASE @LoginPeriod WHEN 1 THEN [dblWorkT1] WHEN 2 THEN [dblWorkT2] WHEN 3 THEN [dblWorkT3] WHEN 4 THEN [dblWorkT4] WHEN 5 THEN [dblWorkT5] WHEN 6 THEN [dblWorkT6] WHEN 7 THEN [dblWorkT7] WHEN 8 THEN [dblWorkT8] WHEN 9 THEN [dblWorkT9] WHEN 10 THEN [dblWorkT10] WHEN 11 THEN [dblWorkT11] ELSE [dblWorkT12] END)) AS dblWorkT,
Sum(fa_Cards.dblBuildArea) AS dblBuildAreaOfSum,
Sum(fa_Cards.lBuildNum) AS lBuildNumOfSum,
Sum(fa_Cards.lMachinNum) AS lMachinNumOfSum,
Sum(fa_Cards.dblMachinW) AS dblMachinWOfSum
FROM ((((fa_Cards INNER JOIN fa_DeprTransactions ON fa_Cards.sCardNum = fa_DeprTransactions.sCardNum) LEFT JOIN fa_WorkLoad ON fa_Cards.sCardNum = fa_WorkLoad.sCardNum) INNER JOIN fa_Departments ON fa_Cards.sDeptNum = fa_Departments.sNum) INNER JOIN fa_AssetTypes ON fa_Cards.sTypeNum = fa_AssetTypes.sNum) INNER JOIN #YXKP ON fa_Cards.sCardID = #YXKP.sCardID
WHERE (((fa_Cards.dDisposeDate) Is Null) AND ((fa_Departments.iLevel)>=@iDeptLevel) AND ((fa_AssetTypes.iLevel)>=@iTypeLevel)) OR (((fa_Cards.dDisposeDate) Is Null) AND ((fa_AssetTypes.iLevel)>=@iTypeLevel) AND ((fa_Departments.lChilds)=@lDeptChilds)) OR (((fa_Cards.dDisposeDate) Is Null) AND ((fa_Departments.iLevel)>=@iDeptLevel) AND ((fa_AssetTypes.lChilds)=@lTypeChilds)) OR (((fa_Cards.dDisposeDate) Is Null) AND ((fa_Departments.lChilds)=@lDeptChilds) AND ((fa_AssetTypes.lChilds)=@lTypeChilds))
GROUP BY (CASE WHEN @iDeptLevel=1 THEN [fa_Departments].[sNum1] ELSE (CASE WHEN @iDeptLevel=2 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] ELSE (CASE WHEN @iDeptLevel=3 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] + [fa_Departments].[sNum3] ELSE (CASE WHEN @iDeptLevel=4 THEN [fa_Departments].[sNum1] + [fa_Departments].[sNum2] + [fa_Departments].[sNum3] + [fa_Departments].[sNum4] ELSE [fa_Departments].[sNum] END) END) END) END), (CASE WHEN @iTypeLevel=1 THEN [fa_AssetTypes].[sNum1] ELSE (CASE WHEN @iTypeLevel=2 THEN [fa_AssetTypes].[sNum1] + [fa_AssetTypes].[sNum2] ELSE (CASE WHEN @iTypeLevel=3 THEN [fa_AssetTypes].[sNum1] + [fa_AssetTypes].[sNum2] + [fa_AssetTypes].[sNum3] ELSE [fa_AssetTypes].[sNum] END) END) END)
---------------------- END ----------------------------
insert into aatjb
SELECT #fa_Q_TJB_Pre1.Num1,
(CASE WHEN [Num1]='' THEN ' 合 计 ' ELSE (CASE WHEN [Num2]='' THEN [Num1] ELSE [Num1] END) END) AS sDeptName,
(CASE WHEN [Num2]='' THEN '' ELSE [Num2] END) AS sTypeName, @LoginPeriod as iperiod,#fa_Q_TJB_Pre1.lCount,
(CASE WHEN [Num1]<>'' THEN (CASE WHEN [iLife]/12>0 THEN CONVERT(varchar(4),[iLife]/12) + '年' ELSE '' END) + (CASE WHEN [ilife]%12>0 THEN CONVERT(varchar(4),[iLife]%12) + '月' ELSE '' END) ELSE '' END) AS Life,
fa_AssetTypes.sUnit, #fa_Q_TJB_Pre1.dblBalanceValue, #fa_Q_TJB_Pre1.dblDeprT,
dblBalanceValue-dblDeprT AS dblBalance,
(dblBalanceValue-dblDeprT)*100/dblBalanceValue AS dblRate,
#fa_Q_TJB_Pre1.dblBVOfSum, #fa_Q_TJB_Pre1.dblDepr, #fa_Q_TJB_Pre1.dblCanWorkOfSum, #fa_Q_TJB_Pre1.dblWorkT,
#fa_Q_TJB_Pre1.dblBuildAreaOfSum, #fa_Q_TJB_Pre1.lBuildNumOfSum, #fa_Q_TJB_Pre1.lMachinNumOfSum, #fa_Q_TJB_Pre1.dblMachinWOfSum
FROM (#fa_Q_TJB_Pre1 LEFT JOIN fa_Departments ON #fa_Q_TJB_Pre1.Num1 = fa_Departments.sNum) LEFT JOIN fa_AssetTypes ON #fa_Q_TJB_Pre1.Num2 = fa_AssetTypes.sNum
ORDER BY #fa_Q_TJB_Pre1.sSort, #fa_Q_TJB_Pre1.Num1, #fa_Q_TJB_Pre1.Num2
delete from aatjb where sTypeName=''
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
第三步：
EXEC fa_Q_TJB_New @LOGINDATE='2006-12-31',@IDEPTLEVEL=-1,@ITYPELEVEL=-1,@LOGINPERIOD=12,@LDEPTCHILDS=0,@LTYPECHILDS=0
第四步：
update fa_total set fa_total.dblValue=aatjb.dblBalanceValue,
fa_total.dblDeprTotal=aatjb.dblDeprT,fa_total.dblDepr=aatjb.dblDepr
from fa_total inner join aatjb on
fa_total.sDeptNum=aatjb.sDeptName and fa_total.sTypeNum=aatjb.sTypeName and
fa_total.iPeriod=aatjb.iperiod
第五步：
drop table [dbo].[aatjb]
|