I am trying to calc carryover of PTO hours from one year to the next. In order to do this, I need to know the previous year’s “remaining balance”, which requires knowing the previous year’s Hours Used AND the previous year’s Carryover. This puts me in a “turtles all the way down” scenario, where I need to reference (prior) carryover in order to calc (current) carryover, going back forever.

To accommodate this, I have come up with a recursive cte. I’ve created a “top level” by simply storing the carryover amounts for my earliest year (2023) in a table, and then starting my cte recursion with that table (adding one year to it each recursion).

The issue isn’t getting it to work, which I’ve done, it’s why it’s taking so long to run.

Here is the cte:

WITH carry AS (SELECT EmployeeNumber, 2023 AS FromYear, CarryoverFrom2023 AS Carryover
            FROM PTO_FixedCarryover
            UNION ALL
            SELECT cur.EmployeeNumber, cur.AbsenceYear, (cur.Potential + prv.Carryover) - cur.HoursUsed AS Carryover
            FROM carry AS prv
            INNER JOIN vwPTO_CarryoverBase AS cur
                ON cur.EmployeeNumber = prv.EmployeeNumber
                AND cur.AbsenceYear = prv.FromYear + 1
            WHERE cur.AbsenceYear <= YEAR(GETDATE())
) 
SELECT * FROM carry

This gives me about 1000 records, half for 2023 and half for 2024. The problem is it takes a few seconds to run. This may not seem like much, but it’s too long for the interface I need this for.

The question is why it takes that long. Essentially, this is running two queries (the FixedCarryover starting point, and one set of records for 2024).

I have run these same two queries, but NOT as a recursive query, and they run instantly. Here’s an example:

WITH carry AS (SELECT EmployeeNumber, 2023 AS FromYear, CarryoverFrom2023 AS Carryover
            FROM PTO_FixedCarryover
)
SELECT * FROM carry
UNION ALL
SELECT cur.EmployeeNumber, cur.AbsenceYear, (cur.Potential + prv.Carryover) - cur.HoursUsed AS Carryover
FROM carry AS prv
INNER JOIN vwPTO_CarryoverBase AS cur
    ON cur.EmployeeNumber = prv.EmployeeNumber
    AND cur.AbsenceYear = prv.FromYear + 1
WHERE cur.AbsenceYear <= YEAR(GETDATE())

Is there something I’m doing wrong here? Is this just an inherent inefficiency in using a recursive cte or am I misunderstanding what it’s doing? Should I be using a different approach entirely?

I should note that the CarryoverBase view is simply SUMing the amount of used hours in each year for each employee (from the Detail-level table) and getting their total Potential hours (which is in a Employee-level table). You can’t used GROUP BY/Aggregates in recursive ctes, so I built a view to do this and JOINed it in the cte. This is, of course, what’s causing my slow performance, but the view itself runs instantly.