T-SQL 计算固定资产折旧（双倍余额法）

同样应用 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(DBBookValue/MidPeriod, 2)
            WHEN NextMo    = Period    THEN CAST(0 AS MONEY)
            WHEN [Month]   > MidPeriod THEN DBBookValue - DBDepAmt
            WHEN YE        = 1         THEN DBBookValue - ROUND(@DBFactor*DBBookValue/Period, 2)
            ELSE DBBookValue - DBDepAmt END
        ,CASE WHEN [Month] = MidPeriod THEN DBCumDep + ROUND(DBBookValue/MidPeriod, 2)
            WHEN NextMo    = Period    THEN DBCumDep + DBBookValue
            WHEN [Month]   > MidPeriod THEN DBCumDep + DBDepAmt
            WHEN YE        = 1         THEN DBCumDep + ROUND(@DBFactor*DBBookValue/Period, 2)
            ELSE DBCumDep + DBDepAmt END
    FROM DBDepSched
       CROSS APPLY (SELECT NextMo=[Month]+1, MidPeriod=Period/2, YE=([Month]+1)/12) x
    WHERE [Month] < Period
)
SELECT AssetID, [Month], DBDepAmt, DBBookValue, DBCumDep
 FROM DBDepSched 
 ORDER BY AssetID, [Month]

计算机0416.675000.000.00
计算机1416.674583.33416.67
计算机2416.674166.66833.34
计算机3416.673749.991250.01
计算机4416.673333.321666.68
计算机5416.672916.652083.35
计算机6416.672499.982500.02
计算机7416.672083.312916.69
计算机8416.671666.643333.36
计算机9416.671249.973750.03
计算机10416.67833.304166.70
计算机11416.67416.634583.37
计算机1234.72381.914618.09
计算机1331.83350.084649.92
计算机1431.83318.254681.75
计算机1531.83286.424713.58
计算机1631.83254.594745.41
计算机1731.83222.764777.24
计算机1831.83190.934809.07
计算机1931.83159.104840.90
计算机2031.83127.274872.73
计算机2131.8395.444904.56
计算机2231.8363.614936.39
计算机2331.8331.784968.22
计算机2431.780.005000.00