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(DBBo