| 解决方案： |
首先进行一次折旧记提操作，不要制单，然后执行如下sql语句即可：
USE UFDATA_002_2003
GO
select a.* into aaa from fa_total as a inner join
Select fa_Cards.sDeptNum,fa_Cards.sTypeNum
FROM fa_Origins AS fa_Origins_2 RIGHT JOIN
fa_Origins AS fa_Origins_1 RIGHT JOIN
fa_Cards LEFT JOIN fa_DeprTransactions ON fa_Cards.sCardNum =
fa_DeprTransactions.sCardNum LEFT JOIN fa_WorkLoad
ON fa_Cards.sCardNum = fa_WorkLoad.sCardNum
LEFT JOIN fa_Departments ON
fa_Cards.sDeptNum = fa_Departments.sNum
LEFT JOIN fa_AssetTypes ON
fa_Cards.sTypeNum = fa_AssetTypes.sNum
LEFT JOIN fa_Status ON
fa_Cards.sStatusID = fa_Status.sID
LEFT JOIN fa_Depreciations ON
fa_Cards.sDeprMethodID = fa_Depreciations.sID
LEFT JOIN fa_ItemsManual ON
fa_Cards.sCardNum = fa_ItemsManual.sCardNum
LEFT JOIN fa_Origins ON fa_Cards.sOrgID = fa_Origins.sID
ON fa_Origins_1.sAddID = fa_Cards.sOrgAddID
ON fa_Origins_2.sDecID = fa_Cards.sOrgDisposeID
LEFT JOIN fa_Vouchers ON fa_Cards.sVoucherNum = fa_Vouchers.sNum
Where fa_Cards.sCardID IN SELECT MaxsCardID From fa_Cards
WHERE fa_Cards.dInputDate<=‘2003-01-31‘
AND fa_Cards.dTransDate<=‘2003-01-31‘ Or fa_Cards.dTransDate
Is Null AND fa_Cards.dDisposeDate<=‘2003-01-31‘ Or
fa_Cards.dDisposeDate Is Null
GROUP BY fa_Cards.sCardNum
AND fa_Cards.iOptType<>8
And fa_Cards.iOptType<>6
AND fa_Cards.dDisposeDate is null
group by fa_Cards.sDeptNum,fa_Cards.sTypeNum as b
on a.sDeptNum=b.sDeptNum and a.sTypeNum=b.sTypeNum
go
delete from fa_total
go
Insert into fa_total
select * from aaa
go
drop table aaa
go
update fa_total
set dblMonthDeprTotal=b.sum3,dblDeprTotal = b.sum1,dblvalue=b.sum2,dblMonthValue=b.sum2,dblYearValue=b.sum2
from fa_total as a inner join Select fa_Cards.sDeptNum,fa_Cards.sTypeNum,
Sumfa_DeprTransactions.dblDeprT1 as sum1 ,sumfa_Cards.dblvalue as sum2,sumfa_DeprTransactions.dblDeprT0 as sum3
FROM fa_Origins AS fa_Origins_2 RIGHT JOIN
fa_Origins AS fa_Origins_1 RIGHT JOIN
fa_Cards LEFT JOIN fa_DeprTransactions ON fa_Cards.sCardNum =
fa_DeprTransactions.sCardNum LEFT JOIN fa_WorkLoad
ON fa_Cards.sCardNum = fa_WorkLoad.sCardNum
LEFT JOIN fa_Departments ON
fa_Cards.sDeptNum = fa_Departments.sNum
LEFT JOIN fa_AssetTypes ON
fa_Cards.sTypeNum = fa_AssetTypes.sNum
LEFT JOIN fa_Status ON
fa_Cards.sStatusID = fa_Status.sID
LEFT JOIN fa_Depreciations ON
fa_Cards.sDeprMethodID = fa_Depreciations.sID
LEFT JOIN fa_ItemsManual ON
fa_Cards.sCardNum = fa_ItemsManual.sCardNum
LEFT JOIN fa_Origins ON fa_Cards.sOrgID = fa_Origins.sID
ON fa_Origins_1.sAddID = fa_Cards.sOrgAddID
ON fa_Origins_2.sDecID = fa_Cards.sOrgDisposeID
LEFT JOIN fa_Vouchers ON fa_Cards.sVoucherNum = fa_Vouchers.sNum
Where fa_Cards.sCardID IN SELECT MaxsCardID From fa_Cards
WHERE fa_Cards.dInputDate<=‘2003-01-31‘
AND fa_Cards.dTransDate<=‘2003-01-31‘ Or fa_Cards.dTransDate
Is Null AND fa_Cards.dDisposeDate<=‘2003-01-31‘ Or
fa_Cards.dDisposeDate Is Null
GROUP BY fa_Cards.sCardNum
AND fa_Cards.iOptType<>8
And fa_Cards.iOptType<>6
AND fa_Cards.dDisposeDate is null
group by fa_Cards.sDeptNum,fa_Cards.sTypeNum as b
on a.sDeptNum=b.sDeptNum and a.sTypeNum=b.sTypeNum
go |