| 解决方案： |
由卡片重新计算折旧表
创建临时表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 ,
--[iPeriod] [smallint] 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<13
BEGIN
print '期间是：' + cast(@iperiod AS char(2) )
--清空临时表
--取月末的日期
set @LoginDate=(select top 1 dend
from ufsystem..ua_period
where cacc_id=@acc_id and iyear=@acc_year and iid=@iperiod)
truncate table fq
truncate table fa
--保存有效卡片id到fa
INSERT INTO fq([sCardID])
SELECT Max([sCardID]) FROM fa_Cards
WHERE (
(dInputDate<=@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
|