Recursive CTE to insert an entry for every day

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

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.