| 解决方案： |
安装SQL2000 SP3 或者修改数据库中的存储过程fa_Q_Zz为：CREATE PROC fa_Q_ZZ(@sTypeNum varchar(50), @sDeptNum varchar(50)) AS
--------------------------- Create #fa_Q_ZZ_Pre1 ---------------
SELECT Top 1 '0' AS sSort, 0 AS sPeriod, 0 AS dblAddValueOfSum, 0 AS dblDecValueOfSum,
Sum(fa_Total.dblMonthValue) AS dblBalance, 0 AS dblTransOutDeprT, 0 AS dblTransInDeprT,
Sum(fa_Total.dblMonthDeprTotal) AS dblDeprT,
(Sum(fa_Total.dblMonthValue)-Sum(fa_Total.dblMonthDeprTotal)) AS dblNetValue
INTO #fa_Q_ZZ_Pre1_temp
FROM fa_Total
WHERE (((fa_Total.sDeptNum) Like @sDeptNum + '%') AND ((fa_Total.sTypeNum) Like @sTypeNum + '%'))
GROUP BY fa_Total.iPeriod order by fa_Total.iPeriod
SELECT *
INTO #fa_Q_ZZ_Pre1
FROM #fa_Q_ZZ_Pre1_temp
UNION
SELECT '1' AS sSort, fa_Total.iPeriod AS sPeriod, Sum(fa_Total.dblAddValue) AS dblAddValueOfSum,
Sum(fa_Total.dblDecValue) AS dblDecValueOfSum, Sum(fa_Total.dblValue) AS dblBalance,
Sum(fa_Total.dblTransOutDeprTotal) AS dblTransOutDeprT,
Sum([dblTransInDeprTotal]+[dblDepr]) AS dblTransInDeprT ,
Sum(fa_Total.dblDeprTotal) AS dblDeprT,
(Sum(fa_Total.dblValue)- Sum(fa_Total.dblDeprTotal)) AS dblNetValue
FROM fa_Total
WHERE (((fa_Total.sDeptNum) Like @sDeptNum + '%') AND ((fa_Total.sTypeNum) Like @sTypeNum + '%') AND ((fa_Total.iPeriod)<=[iPeriod]))
GROUP BY fa_Total.iPeriod
UNION
SELECT '2' AS sSort, 13 AS sPeriod, Sum(fa_Total.dblAddValue) AS dblAddValueOfSum,
Sum(fa_Total.dblDecValue) AS dblDecValueOfSum, Sum(fa_Total.dblValue) AS dblBalance,
Sum(fa_Total.dblTransOutDeprTotal) AS dblTransOutDeprT,
Sum([dblTransInDeprTotal]+[dblDepr]) AS dblTransInDeprT,
Sum(fa_Total.dblDeprTotal) AS dblDeprT,
(Sum(fa_Total.dblValue)-Sum(fa_Total.dblDeprTotal)) AS dblNetValue
FROM fa_Total
WHERE (((fa_Total.sDeptNum) Like @sDeptNum + '%') AND ((fa_Total.sTypeNum) Like @sTypeNum + '%'))
--------------------------- Create #fa_Q_ZZ_Pre2 ---------------
SELECT '2' AS sSort, 13 AS sPeriod,
Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblValue] ELSE 0 END)) AS dblAddValue,
Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblValue] END)) AS dblDecValue,
0 AS dblBalanceValue,
Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblTransOutDeprTCard] END)) AS dblTransOutDeprT,
Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblTransInDeprTCard] ELSE 0 END)) AS dblTransInDeprT,
0 AS dblDeprT, 0 AS dblBalance
INTO #fa_Q_ZZ_Pre2
FROM fa_Cards
WHERE (((fa_Cards.sTypeNum) Like @sTypeNum + '%') AND ((fa_Cards.sDeptNum) Like @sDeptNum + '%') AND (([fa_Cards].[sTransTypeNum]) Like @sTypeNum + '%') AND (([fa_Cards].[sTransDepteNum]) Like @sDeptNum + '%') AND ((fa_Cards.iOptType) Between 6 And 9))
UNION
SELECT '1' AS sSort, [iTransPeriod] AS sPeriod,
Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblValue] ELSE 0 END)) AS dblAddValue,
Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblValue] END)) AS dblDecValue, 0 AS dblBalanceValue,
Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblTransOutDeprTCard] END)) AS dblTransOutDeprT,
Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblTransInDeprTCard] ELSE 0 END)) AS dblTransInDeprT,
0 AS dblDeprT, 0 AS dblBalance
FROM fa_Cards
WHERE (((fa_Cards.sTypeNum) Like @sTypeNum + '%') AND ((fa_Cards.sDeptNum) Like @sDeptNum + '%') AND (([fa_Cards].[sTransTypeNum]) Like @sTypeNum + '%') AND (([fa_Cards].[sTransDepteNum]) Like @sDeptNum + '%') AND ((fa_Cards.iOptType) Between 6 And 9))
GROUP BY iTransPeriod
--------------------------------- END -------------------------
SELECT (CASE #fa_Q_ZZ_Pre1.sPeriod WHEN 0 THEN '上期结转' WHEN 13 THEN '合计' ELSE CONVERT(varchar(2),#fa_Q_ZZ_Pre1.sPeriod) END) AS sPeriod,
(CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE #fa_Q_ZZ_Pre1.[dblAddValueOfSum]-ISNULL([dblAddValue],0) END) AS dblDebitValue,
(CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE #fa_Q_ZZ_Pre1.[dblDecValueOfSum]-ISNULL([dblDecValue],0) END) AS dblCreditValue,
#fa_Q_ZZ_Pre1.dblBalance,
(CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE [#fa_Q_ZZ_Pre1].[dblTransOutDeprT]-ISNULL([#fa_Q_ZZ_Pre2].[dblTransOutDeprT],0) END) AS dblDebitDeprT,
(CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE [#fa_Q_ZZ_Pre1].[dblTransInDeprT]-ISNULL([#fa_Q_ZZ_Pre2].[dblTransInDeprT],0) END) AS dblCreditDeprT,
#fa_Q_ZZ_Pre1.dblDeprT, #fa_Q_ZZ_Pre1.dblNetValue ,[#fa_Q_ZZ_Pre1].[sPeriod] AS OrderPeriod
FROM #fa_Q_ZZ_Pre1 LEFT JOIN #fa_Q_ZZ_Pre2 ON #fa_Q_ZZ_Pre1.sPeriod = #fa_Q_ZZ_Pre2.sPeriod
ORDER BY #fa_Q_ZZ_Pre1.sSort, [#fa_Q_ZZ_Pre1].[sPeriod]
GO
|