0

Consider the following:

IF OBJECT_ID('tempdb..#TetstData') IS NOT NULL
    DROP TABLE #TetstData
 
CREATE TABLE #TetstData
(
    Id INT NOT NULL, 
    PurchaseDate DATE NOT NULL,
    DepreciationRate DECIMAL(18,9)
)

INSERT INTO #TetstData VALUES(1, '2019/01/01', 0.123456789)
INSERT INTO #TetstData VALUES(2, '2021/01/01', 0.987654321)

;WITH cte_daily AS
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY t.PurchaseDate ASC) AS rowNumber,
        t.Id, 
        t.PurchaseDate, 
        t.DepreciationRate,
        0 AS PreviousDepreciationRate
    FROM
        #TetstData t

    UNION ALL

    SELECT
        ROW_NUMBER() OVER (PARTITION BY t.Id ORDER BY t.PurchaseDate ASC) AS rowNumber,
        t.Id, 
        DATEADD(DAY, 1, t.PurchaseDate) as PurchaseDate, 
        t.DepreciationRate, 
        0 AS PreviousDepreciationRate --access previous iteration value, if possible
    FROM
        #TetstData t 
    INNER JOIN
        cte_daily c ON t.Id = c.Id
    WHERE
        t.PurchaseDate < GETDATE()
)
SELECT * 
FROM cte_daily
OPTION (maxrecursion 0)

I need to create an entry, for everyday, from the PurchaseDate until the current date. I have tried with a recursive CTE, but unfortunately, it does not give the desired results.

I am not sure, if this is even possible with a CTE. Any help would be appreciated

Improve this question
1
  • 2
    What does the final expected output look like? Does the DepreciationRate get replicated across the new dates? Commented Jun 22 at 15:02

4 Answers 4

Reset to default
2

The main error that makes your recursive CTE loop indefinitely if that you compare to t instead of c.
In your recursive part SELECT … DATEADD(DAY, 1, t.PurchaseDate) … FROM #TetstData t … WHERE t.PurchaseDate < GETDATE(),
you're basing your stop condition of the loop onto t which does not change (t.PurchaseDate is always 2019/01/01, thus it will never be >= GETDATE() which would stop the loop).
In the same way, the increment being based on t.PurchaseDate, will always be 2019/01/02 on the next iteration of the loop for this Id.

Thus make sure your loop condition, and the recursively built row, are based on the recursive CTE itself, not on the "static" part:

DATEADD(DAY, 1, c.PurchaseDate), … WHERE c.PurchaseDate < GETDATE()

To prevent this kind of error, I would recommend to never use OPTION (maxrecursion 0) until your loop is working:
you could have tested with a date less than 100 days before today, or (if running on a real dataset with 2021) having added a condition to filter to id 2 with a maxrecursion 2000 (3 years is approximately 1000 days, so 2000 days is reasonable for a quick test of 2021).

By simply applying these rules you'll be able to see you loop run (see it in a test fiddle),
and then work on your query to refine it, use DepreciationRate, and so on.

You'll probably converge to a query such as the one Chris Maurer gave in his answer, so I won't replicate it here.

However two points that maybe need an explanation:

  • Chris said you don't need to join to #TetstData in your recursive CTE: that's right, after having replaced all ts by c where necessary, you'll see that once launched by the initialization part of the recursive CTE, your query can probably run thanks to only the previous iteration: depending on if you've stuffed every necessary info for the iterations into the recursive columns, you probably don't need to refer to the original table anymore.
    A use case where you could rejoin to #TetstData would be if the end date wasn't GETDATE() (which is independant from t), but a column of t.
    But even then you could pass this endate as an additional column to cte_daily, to compare c.ShiftedPurchaseDate < c.CopiedResoldDate instead of c.ShiftedPurchaseDate < t.ResoldDate.
  • As you see in my fiddle, the recursive part of a CTE only sees one previous iteration's result as the contents of the pseudo-table cte_daily. Only once the recursion is finished (outside of cte_daily AS (…)) will cte_daily appear with all its rows.
    As a consequence, row_number() over (…) will always be 1 inside the recursive CTE, because you only have 1 row for this Id from the previous iteration.
    To get your row position:
    • Either wait after the recursive CTE to compute your row_number()
    • Or simply add a rowNumber column to your recursive CTE, starting at 1 in the initialization (left part of the UNION ALL), and incremented "by hand" as c.rowNumber + 1 in the recursive part

I won't talk about PreviousDepreciationRate, which is quite unclear in your question and that you should explain by giving some example rows with the value you expect in it.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you, you have mentioned quite a few mistakes I made, without me even realising/thinking about it. Between the answers provided by yourself and Chris, I have managed to get my recursive cte working.
2

Skip the table joins, take what you need initially from tetsdata and propagate them forward in the recursion.

With cte_daily As (
  Select id, PurchaseDate as OriginalPurchase, PurchaseDate as DepreciationDate,
      0 as ElapsedDays, DepreciationRate, cast(1 as Decimal) as CumDepreciation
  From tetsdata 
Union All
  Select t.id, t.PurchaseDate, dateadd(day,1,t.DepreciationDate),
      T.ElapsedDays + 1, t.DepreciationRate, t.CumDepreciation*t.DepreciationRate
  From cte_daily
  Where t.DepreciationDate < GETDATE()
Improve this answer

Comments

2

The depreciation rate is the amount of decrease in the book value of an accounting unit over a year. You want to calculate the amount of depreciation for each day since the purchase of the property.

Daily value is DepreciationRate/(Days in year). We should consider also number of days in year (365 or 366). So

cross apply( values 
 ( datediff(day
  ,DateFromParts (year(dateadd(day,(DepreciationDay+0),r.PurchaseDate)),01,01)
  ,DateFromParts(year(dateadd(day,(DepreciationDay+0),r.PurchaseDate)),12,31))+1
  ))n(dy) -- days in year

Recursive query adds day by day until limit date and increments Depreciation Rate by day value up to limit 1.0

      case when (DepreciationDay+1.0)*DepreciationRate/n.dy<1.0 then
        cast((DepreciationDay+1.0)*DepreciationRate/n.dy as decimal(18,9)) 
      else cast(1.0 as decimal(18,9))
      end as DepreciationPct ,

See example for SQL Server.

-- INSERT INTO #TetstData VALUES(1, '2019/01/01', 0.123456789);
INSERT INTO #TetstData VALUES(1, '2019/01/01', 0.363456789);
INSERT INTO #TetstData VALUES(2, '2021/01/01', 0.987654321);

WITH cte_daily AS
(
    SELECT  t.Id, t.PurchaseDate, t.PurchaseDate as DepreciationDate,
      cast(1.0*t.DepreciationRate/dy as decimal(18,9)) as DepreciationPct,
      t.DepreciationRate,dy, 1 as DepreciationDay,
      1 as lvl
    FROM #TetstData t
    cross apply( values (datediff(day,DateFromParts (year(t.PurchaseDate),01,01)
                            ,DateFromParts(year(t.PurchaseDate),12,31))+1
               ))n(dy)
  union all
    SELECT  r.Id, r.PurchaseDate, dateadd(day,(DepreciationDay+0),r.PurchaseDate) as DepreciationDate,
      case when (DepreciationDay+1.0)*DepreciationRate/n.dy<1.0 then
        cast((DepreciationDay+1.0)*DepreciationRate/n.dy as decimal(18,9)) 
      else cast(1.0 as decimal(18,9))
      end as DepreciationPct ,
      r.DepreciationRate,n.dy, (DepreciationDay+1) as DepreciationDay,
      lvl+1 as lvl
    FROM cte_daily r
    cross apply( values 
         (datediff(day
                ,DateFromParts (year(dateadd(day,(DepreciationDay+0),r.PurchaseDate)),01,01)
                ,DateFromParts(year(dateadd(day,(DepreciationDay+0),r.PurchaseDate)),12,31))+1
               ))n(dy)
    where dateadd(day,(DepreciationDay+0),r.PurchaseDate)<'2023-01-01'
      and r.DepreciationPct<1
)
SELECT * 
FROM cte_daily
order by id,DepreciationDate
option(maxrecursion 1600))
Id PurchaseDate DepreciationDate DepreciationPct DepreciationRate dy DepreciationDay lvl
1 2019-01-01 2019-01-01 0.000995772 0.363456789 365 1 1
1 2019-01-01 2019-01-02 0.001991544 0.363456789 365 2 2
1 2019-01-01 2019-01-03 0.002987316 0.363456789 365 3 3
1 2019-01-01 2019-01-04 0.003983088 0.363456789 365 4 4
1 2019-01-01 2019-01-05 0.004978860 0.363456789 365 5 5
1 2019-01-01 2019-01-06 0.005974632 0.363456789 365 6 6
1 2019-01-01 2019-01-07 0.006970404 0.363456789 365 7 7
1 2019-01-01 2019-01-08 0.007966176 0.363456789 365 8 8
1 2019-01-01 2019-01-09 0.008961948 0.363456789 365 9 9
1 2019-01-01 2019-01-10 0.009957720 0.363456789 365 10 10
1 2019-01-01 2019-01-11 0.010953492 0.363456789 365 11 11
1 2019-01-01 2019-01-12 0.011949264 0.363456789 365 12 12
1 2019-01-01 2019-01-13 0.012945036 0.363456789 365 13 13
1 2019-01-01 2019-01-14 0.013940808 0.363456789 365 14 14
1 2019-01-01 2019-01-15 0.014936580 0.363456789 365 15 15
1 2019-01-01 2019-01-16 0.015932352 0.363456789 365 16 16
1 .......... ............ ............ ........... ... . .
1 2019-01-01 2019-12-26 0.358477929 0.363456789 365 360 360
1 2019-01-01 2019-12-27 0.359473701 0.363456789 365 361 361
1 2019-01-01 2019-12-28 0.360469473 0.363456789 365 362 362
1 2019-01-01 2019-12-29 0.361465245 0.363456789 365 363 363
1 2019-01-01 2019-12-30 0.362461017 0.363456789 365 364 364
1 2019-01-01 2019-12-31 0.363456789 0.363456789 365 365 365
1 2019-01-01 2020-01-01 0.364452561 0.363456789 365 366 366
1 2019-01-01 2020-01-02 0.365448333 0.363456789 365 367 367
1 2019-01-01 2020-01-03 0.366444105 0.363456789 365 368 368
1 2019-01-01 2020-01-04 0.367439877 0.363456789 365 369 369
1 2019-01-01 2020-01-05 0.368435649 0.363456789 365 370 370
1 .......... ............ ............ ........... ... . .
1 2019-01-01 2020-12-25 0.721934718 0.363456789 365 725 725
1 2019-01-01 2020-12-26 0.722930490 0.363456789 365 726 726
1 2019-01-01 2020-12-27 0.723926262 0.363456789 365 727 727
1 2019-01-01 2020-12-28 0.724922034 0.363456789 365 728 728
1 2019-01-01 2020-12-29 0.725917806 0.363456789 365 729 729
1 2019-01-01 2020-12-30 0.726913578 0.363456789 365 730 730
1 2019-01-01 2020-12-31 0.727909350 0.363456789 365 731 731
1 2019-01-01 2021-01-01 0.728905122 0.363456789 365 732 732
1 2019-01-01 2021-01-02 0.729900894 0.363456789 365 733 733
1 2019-01-01 2021-09-24 0.993780481 0.363456789 365 998 998
1 2019-01-01 2021-09-25 0.994776253 0.363456789 365 999 999
1 2019-01-01 2021-09-26 0.995772025 0.363456789 365 1000 1000
1 2019-01-01 2021-09-27 0.996767797 0.363456789 365 1001 1001
1 2019-01-01 2021-09-28 0.997763569 0.363456789 365 1002 1002
1 2019-01-01 2021-09-29 0.998759341 0.363456789 365 1003 1003
1 2019-01-01 2021-09-30 0.999755113 0.363456789 365 1004 1004
1 2019-01-01 2021-10-01 1.000000000 0.363456789 365 1005 1005
2 2021-01-01 2021-01-01 0.002705902 0.987654321 365 1 1
2 2021-01-01 2021-01-02 0.005411804 0.987654321 365 2 2
2 2021-01-01 2021-01-03 0.008117707 0.987654321 365 3 3
2 2021-01-01 2021-01-04 0.010823609 0.987654321 365 4 4
2 2021-01-01 2021-01-05 0.013529511 0.987654321 365 5 5
2 2021-01-01 2021-01-06 0.016235413 0.987654321 365 6 6
2 2021-01-01 2021-01-07 0.018941316 0.987654321 365 7 7
2 .......... ............ ............ ........... ... . .
2 2021-01-01 2021-12-27 0.976830712 0.987654321 365 361 361
2 2021-01-01 2021-12-28 0.979536614 0.987654321 365 362 362
2 2021-01-01 2021-12-29 0.982242517 0.987654321 365 363 363
2 2021-01-01 2021-12-30 0.984948419 0.987654321 365 364 364
2 2021-01-01 2021-12-31 0.987654321 0.987654321 365 365 365
2 2021-01-01 2022-01-01 0.990360223 0.987654321 365 366 366
2 2021-01-01 2022-01-02 0.993066125 0.987654321 365 367 367
2 2021-01-01 2022-01-03 0.995772028 0.987654321 365 368 368
2 2021-01-01 2022-01-04 0.998477930 0.987654321 365 369 369
2 2021-01-01 2022-01-05 1.000000000 0.987654321 365 370 370

The rules for calculating depreciation may differ slightly from those given by me, but they are basically the same.

fiddle

Improve this answer

3 Comments

Thanks for the attempt to explain DepreciationRate, which I was unclear with, except knowing that it's generally an annual depreciation (as you did) instead of a daily one. OP's test values of 0.123… and 0.987… don't help understanding. However, I seem to remember that, instead of linear, this depreciation should be somewhat of a logarithm?
Yes, there are several ways to calculate depreciation, including logarithmic. Usually, the method is set from the business side and is performed monthly or annually. I think none of them involves daily calculation for accounting purposes, only for internal purposes. I applied the linear approach as the simplest, until clarifying the OP's purpose. Linear method applicable for method "Units of Production Method".
Intuitively, if you have a depreciation of 0.5 per year, your item costing 100 should be valued only 50 after 1 year. However if we interrupt this after six month, with a linear depreciation it will be valued 75, and then if we reapply a linear depreciation for the remaining 6 months of the year we end up with 56.25 (75*0.75), not 50. I'd say that it's not depreciation*(days/365), but instead pow(depreciation, days/365): 0.5 to the power of 6 months (0.5 of 365) is sqrt(2)/2 = 0.707, and it works: after 6 month your product is valued 70.7, after 6 months + 6 months it's 70.7*0.707 = 50
0

Using SQL Server version 2022, you query can be simplified by :

SELECT ROW_NUMBER() OVER (PARTITION BY Id ORDER BY t.PurchaseDate ASC) AS rowNumber,
       t.Id, DATEADD(day, value, t.PurchaseDate) AS PurchaseDate, t.DepreciationRate, 0.0 AS PreviousDepreciationRate
 FROM  #TetstData t
       CROSS APPLY generate_series(0, DATEDIFF(day, PurchaseDate, GETDATE()))
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.