| 解决方案： |
这类问题与固定资产对帐不平的原理是一样的。请详细阅读支持网站的维护案例“zc20040408 U8固定资产年度结转后账表累计折旧不平探讨 ”。 现在将其中的语句教本列在此处
--由卡片重新计算折旧表
--创建临时表fq存放当月有效的卡片id号，fa存放卡片相关数据的合计值
--判断是用友数据库否则退出
if exists(select * from master..sysdatabases where name=db_name() and name like 'ufdata[_]___[_]____'
and ISNUMERIC (substring(name,8,3))=1 and ISNUMERIC(substring(name,12,4))=1)
begin
begin TRANSACTION kk
if exists (select * from dbo.sysobjects where id = object_id(N'[fq]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [fq]
if exists (select * from dbo.sysobjects where id = object_id(N'[fa]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [fa]
CREATE TABLE fq([sCardID] int ,PRIMARY key CLUSTERED ([sCardID]))
CREATE TABLE fa ( [sDeptNum] [varchar] (12) NOT NULL ,
[sTypeNum] [varchar] (10) NOT NULL ,
[dblDeprTotal] [float] NULL ,
[dblDepr] [float] NULL ,
PRIMARY KEY CLUSTERED ( [sDeptNum], [sTypeNum] ) )
commit TRANSACTION kk
--初始化
declare @LoginDate DateTime
DECLARE @acc_id varchar(10)
declare @acc_year varchar(10)
declare @iPeriod int
--得到当前本数据库的有关信息
set @acc_id=substring(db_name(),8,3)
set @acc_year=substring(db_name(),12,4)
--取开始的期间
set @iPeriod=(select min(iid) from ufsystem..ua_period where cacc_id=@acc_id and iyear=@acc_year)
if not @iperiod between 1 and 12
begin
return
end
--循环到年末
WHILE @iperiod<=@LoginDate) AND (dTransDate<=@LoginDate Or dTransDate Is Null)
AND (dDisposeDate<=@LoginDate Or dDisposeDate Is Null))
GROUP BY sCardNum
--无卡片则退出
IF @@ROWCOUNT = 0
BEGIN
set @iperiod=@iperiod+1
CONTINUE
END
insert fa([sDeptNum],[sTypeNum],[dblDeprTotal],[dblDepr])
select c.sDeptNum,c.sTypeNum,
sum(case @iPeriod when 1 then d.dblDeprT1
when 2 then d.dblDeprT2
when 3 then d.dblDeprT3
when 4 then d.dblDeprT4
when 5 then d.dblDeprT5
when 6 then d.dblDeprT6
when 7 then d.dblDeprT7
when 8 then d.dblDeprT8
when 9 then d.dblDeprT9
when 10 then d.dblDeprT10
when 11 then d.dblDeprT11
when 12 then d.dblDeprT12
else 0
end) as sum1,
sum(case @iPeriod when 1 then d.dblDepr1 when 2 then d.dblDepr2 when 3 then d.dblDepr3
when 4 then d.dblDepr4 when 5 then d.dblDepr5 when 6 then d.dblDepr6
when 7 then d.dblDepr7 when 8 then d.dblDepr8 when 9 then d.dblDepr9
when 10 then d.dblDepr10 when 11 then d.dblDepr11 when 12 then d.dblDepr12
else 0
end) as sum2
FROM (fq INNER JOIN fa_Cards c ON c.sCardID = fq.sCardID)
INNER JOIN fa_DeprTransactions d ON c.sCardNum = d.sCardNum
WHERE (c.dDisposeDate) Is Null
group by c.sDeptNum,c.sTypeNum
IF @@ROWCOUNT = 0
BEGIN
set @iperiod=@iperiod+1
CONTINUE
END
update fa_Total set dblDeprTotal = fa.dblDeprTotal, dblDepr=fa.dblDepr
from fa_Total t inner join fa on fa.sDeptNum = t.sDeptNum
and t.sTypeNum = fa.sTypeNum
where t.iperiod=@iperiod
set @iperiod=@iperiod+1
end
drop table fq
drop table fa end |