使用 CTE, 计算固定资产折旧(直线法)。
DECLARE @Assets TABLE (NAME VARCHAR(20), PurchaseCost MONEY, Period INT)
INSERT INTO @Assets
SELECT '计算机', 5000, 24
;WITH SLDepSched (AssetID, [Month], Period -- 固定资产
,SLDepAmt, SLBookValue, SLCumDep -- 直线法
) AS (
SELECT NAME, 0, Period
,ROUND(PurchaseCost/Period, 2) -- 直线法折旧额
,PurchaseCost, CAST(0 AS MONEY)
FROM @Assets
UNION ALL
SELECT AssetID, [Month]+1, Period
,CASE [Month]+1 WHEN Period THEN SLBookValue ELSE SLDepAmt END
,CASE [Month]+1 WHEN Period THEN CAST(0 AS MONEY) ELSE SLBookValue - SLDepAmt END
,CASE [Month]+1 WHEN Period THEN SLCumDep + SLBookValue ELSE SLCumDep + SLDepAmt END
FROM SLDepSched
WHERE [Month] < Period)
SELECT AssetID, [Month], SLDepAmt, SLBookValue, SLCumDep
FROM SLDepSched
ORDER BY AssetID, [Month]
<