01.
02.
03.
04.
05.
06.
07.
08.
09.
10.
11.
12.DECLARE @Assets TABLE (ID INT, PurchaseCost MONEY, Period INT)
13.DECLARE @DBFactor INT
14.
15.SET @DBFactor = 2
16.
17.INSERT INTO @Assets
18.
19.
20.
21.
22. SELECT 3, 200000, 60
23.;WITH DBDepSched (AssetID, [Month], Period
24. ,DBDepAmt, DBBookValue, DBCumDep
25. ) AS (
26. SELECT ID, 0, Period
27. ,ROUND(2*PurchaseCost/Period, 2)
28. ,PurchaseCost, CAST(0 AS MONEY)
29. FROM @Assets
30. UNION ALL
31. SELECT AssetID, NextMo, Period
32. ,CASE WHEN [Month] = MidPeriod THEN ROUND(DBBookValue/MidPeriod, 2)
33. WHEN NextMo = Period THEN DBBookValue
34. WHEN [Month] > MidPeriod THEN DBDepAmt
35. WHEN YE = 1 THEN ROUND(@DBFactor*DBBookValue/Period, 2)
36. ELSE DBDepAmt END
37. ,CASE WHEN [Month] = MidPeriod THEN DBBookValue - ROUND(DBBookValue/MidPeriod, 2)
38. WHEN NextMo = Period THEN CAST(0 AS MONEY)
39. WHEN [Month] > MidPeriod THEN DBBookValue - DBDepAmt
40. WHEN YE = 1 THEN DBBookValue - ROUND(@DBFactor*DBBookValue/Period, 2)
41. ELSE DBBookValue - DBDepAmt END
42. ,CASE WHEN [Month] = MidPeriod THEN DBCumDep + ROUND(DBBookValue/MidPeriod, 2)
43. WHEN NextMo = Period THEN DBCumDep + DBBookValue
44. WHEN [Month] > MidPeriod THEN DBCumDep + DBDepAmt
45. WHEN YE = 1 THEN DBCumDep + ROUND(@DBFactor*DBBookValue/Period, 2)
46. ELSE DBCumDep + DBDepAmt END
47. FROM DBDepSched
48. CROSS APPLY (SELECT NextMo=[Month]+1, MidPeriod=Period/2, YE=([Month]+1)/12) x
49. WHERE [Month] < Period
50.)
51.SELECT *
52. FROM DBDepSched
53. ORDER BY AssetID, [Month]
