Styjun

I'm trying to calculate depreciation on vehicles. If there is a rebate on a vehicle, I need to stop the depreciation, factor in the rebaste based on the month it look affect, and resume the depreciation calculation.

A vehicle depreciates at a flat rate of 2% every month with 50 months being the point of 100% depreciation. When a rebate appears, I can stop the depreciation, but I don't know how to make it start again from a certain month.

Below is an example of the table's deprecation up to directly before the rebate:

+----------+-------+------------+--------------+------------+------------+
| Vehicle# | month | depDate | Initial Cost | Monthlydep | totaldep |
+----------+-------+------------+--------------+------------+------------+
| 12451 | 1 | 2015-08-01 | 44953.24 | 899.06 | 899.0648 |
| 12451 | 2 | 2015-09-01 | 44953.24 | 899.06 | 1798.1296 |
| ------- | ----- | ----- | ----- | ----- | ----- |
| 12451 | 42 | 2019-01-01 | 44953.24 | 899.06 | 37760.7216 |
| 12451 | 43 | 2019-02-01 | 44953.24 | 899.06 | 38659.7864 |
+----------+-------+------------+--------------+------------+------------+

Then let's say that a rebate comes in this month (2019-03-01) it needs to be factored in and then the depreciation needs to be recalculated from that month onwards the. How do I restart the depreciation from month 43 instead of it going through everything?

For example let's say that we get a rebate in month 44 for $200 dollars. The table should look like something below:

+----------+-------+------------+--------------+------------+------------+
| Vehicle# | month | depDate | Initial Cost | Monthlydep | totaldep |
+----------+-------+------------+--------------+------------+------------+
| 12451 | 43 | 2019-02-01 | 44953.24 | 899.06 | 38659.7864 |
| 12451 | 44 | 2019-03-01 | 44953.24 | 1099.06 | 39758.8464 |
| 12451 | 45 | 2019-04-01 | 44953.24 | 1099.06 | 40857.9064 |
| 12451 | 46 | 2019-05-01 | 44953.24 | 1099.06 | 41956.9664 |
| 12451 | 47 | 2019-06-01 | 44953.24 | 1099.06 | 43056.0264 |
| 12451 | 48 | 2019-06-01 | 44953.24 | 1099.06 | 44155.0864 |
| 12451 | 49 | 2019-06-01 | 44953.24 | 1099.06 | 45254.1464 |
+----------+-------+------------+--------------+------------+------------+

So month 49 would be the final month because the totalDep is equal to or higher than the initial cost

My sample code is below. If you remove the first cte and the join inner join in the top part of the union then that is the working depreciation calculation:

;With cte As( Select bd.[VehicleID]
 ,Max(bd.[Month]) As month
 ,Max(DateAdd(DAY,1,EOMONTH(DepreciationReportDate,-1))) As DepreciationReportDate
 ,Max(bd.MonthlyDepreciation) As MonthlyDepreciation
 ,Max(bd.AdjustedPurchaseCost) As AdjustedPurchaseCost
 ,Max(AccumulatedDepreciation) As AccumulatedDepreciation
 From Work.dbo.DepreciationSchedule bd
 Group By bd.VehicleID
)
,cte_CreateRows As 
 (
 Select bd.[VehicleID]
 ,bd.[Month]
 ,DATEADD(DAY,1,EOMONTH(bd.DepreciationReportDate,-1)) As DepreciationReportDate
 ,bd.MonthlyDepreciation
 ,bd.AdjustedPurchaseCost
 ,bd.AccumulatedDepreciation
 From Work.dbo.DepreciationSchedule bd
 Inner Join cte cte 
 On cte.VehicleID = bd.VehicleID
 And cte.month = bd.Month
 Union All
 Select bd.[VehicleID]
 ,[Month] = Cast(cr.[Month]+1 As int)
 ,DATEADD(DAY,1,EOMONTH(DateAdd(Month, 1, cr.DepreciationReportDate),-1)) As DepreciationReportDate 
 ,bd.MonthlyDepreciation 
 ,bd.AdjustedPurchaseCost
 ,AccumulatedDepreciation = cr.AccumulatedDepreciation + cr.MonthlyDepreciation 
 From Work.dbo.DepreciationSchedule bd
 Inner Join cte_CreateRows cr On bd.[VehicleID] = cr.[VehicleID]
 Where cr.AccumulatedDepreciation < cr.AdjustedPurchaseCost
 And DateAdd(Month,1, DateAdd(DAY,1,EOMONTH(cr.DepreciationReportDate,-1))) < DATEADD(DAY,1,EOMONTH(GetDate(),-1))
 )
Select a.VehicleID
 ,a.Month
 ,a.DepreciationReportDate
 ,Cast(a.MonthlyDepreciation As Decimal(12,2)) As 'Monthly Depreciation Expense'
 ,a.AdjustedPurchaseCost
 ,a.AccumulatedDepreciation
From [cte_CreateRows] As a
Order By a.VehicleID, a.Month