T-SQL 计算固定资产折旧（直线法）

使用 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]

计算机0208.335000.000.00
计算机1208.334791.67208.33
计算机2208.334583.34416.66
计算机3208.334375.01624.99
计算机4208.334166.68833.32
计算机5208.333958.351041.65
计算机6208.333750.021249.98
计算机7208.333541.691458.31
计算机8208.333333.361666.64
计算机9208.333125.031874.97
计算机10208.332916.702083.30
计算机11208.332708.372291.63
计算机12208.332500.042499.96
计算机13208.332291.712708.29
计算机14208.332083.382916.62
计算机15208.331875.053124.95
计算机16208.331666.723333.28
计算机17208.331458.393541.61
计算机18208.331250.063749.94
计算机19208.331041.733958.27
计算机20208.33833.404166.60
计算机21208.33625.074374.93
计算机22208.33416.744583.26
计算机23208.33208.414791.59
计算机24208.410.005000.00