同样应用 CTE,计算固定资产折旧(双倍余额法)
DECLARE @Assets TABLE (NAME VARCHAR(20), PurchaseCost MONEY, Period INT)
DECLARE @DBFactor INT
SET @DBFactor = 2 -- 双倍
INSERT INTO @Assets
SELECT '计算机', 5000, 24
;WITH DBDepSched (AssetID, [Month], Period -- 固定资产
,DBDepAmt, DBBookValue, DBCumDep -- 双倍余额法
) AS (
SELECT NAME, 0, Period
,ROUND(2*PurchaseCost/Period, 2) -- 双倍余额
,PurchaseCost, CAST(0 AS MONEY)
FROM @Assets
UNION ALL
SELECT AssetID, NextMo, Period
,CASE WHEN [Month] = MidPeriod THEN ROUND(DBBookValue/MidPeriod, 2)
WHEN NextMo = Period THEN DBBookValue
WHEN [Month] > MidPeriod THEN DBDepAmt
WHEN YE = 1 THEN ROUND(@DBFactor*DBBookValue/Period, 2)
ELSE DBDepAmt END
,CASE WHEN [Month] = MidPeriod THEN DBBookValue - ROUND(DBBo