Search code examples
sqlsql-servercommon-table-expressionrecursive-query

How to recursively calculate yearly rollover in SQL?


I need to calculate yearly rollover for a system that keeps track of when people have used days off.
The rollover calculation itself is simple: [TOTALDAYSALLOWED] - [USED]

Provided that number is not higher than [MAXROLLOVER] (and > 0)

Where this gets complicated is the [TOTALDAYSALLOWED] column, which is [NUMDAYSALLOWED] combined with the previous year's rollover to get the total number of days that can be used in a current year.

I've tried several different ways of getting this calculation, but all of them have failed to account for the previous year's rollover being a part of the current year's allowed days.
Creating columns for the LAG of days used, joining the data to itself but shifted back a year, etc. I'm not including examples of code I've tried because the approach was wrong in all of the attempts. That would just make this long post even longer.

Here's the data I'm working with:

Sample data

Here's how it should look after the calculation: desired calculation

This is a per-person calculation, so there's no need to consider any personal ID here. DAYTYPE only has one value currently, but I want to include it in the calculation in case another is added. The [HOW] column is only for clarity in this post.

Here's some code to generate the sample data (SQL Server or Azure SQL):

IF OBJECT_ID('tempdb..#COUNTS') IS NOT NULL DROP TABLE #COUNTS
CREATE TABLE #COUNTS (USED INT, DAYTYPE VARCHAR(20), THEYEAR INT)
INSERT INTO #COUNTS (USED, DAYTYPE, THEYEAR)
SELECT 1, 'X', 2019
UNION
SELECT 3, 'X', 2020
UNION 
SELECT 0, 'X', 2021

IF OBJECT_ID('tempdb..#ALLOWANCES') IS NOT NULL DROP TABLE #ALLOWANCES
CREATE TABLE #ALLOWANCES (THEYEAR INT, DAYTYPE VARCHAR(20), NUMDAYSALLOWED INT, MAXROLLOVER INT)
INSERT INTO #ALLOWANCES (THEYEAR, DAYTYPE, NUMDAYSALLOWED, MAXROLLOVER)
SELECT 2019, 'X', 3, 3
UNION 
SELECT 2020, 'X', 3, 3
UNION 
SELECT 2021, 'X', 3, 3

SELECT C.*, A.NUMDAYSALLOWED, A.MAXROLLOVER
FROM #COUNTS C
JOIN #ALLOWANCES A ON C.DAYTYPE = A.DAYTYPE AND C.THEYEAR = A.THEYEAR

Solution

  • The tricky part is to limit the rollover amount. This is maybe possible with window functions, but I think this is easier to do with a recursive query:

    with 
        data as (
            select c.*, a.numdaysallowed, a.maxrollover,
                row_number() over(partition by c.daytype order by c.theyear) rn 
            from #counts c
        inner join #allowances a on a.theyear = c.theyear and a.daytype = c.daytype
        ),
        cte as (
            select d.*, 
                numdaysallowed as totaldaysallowed,
                numdaysallowed - used as actualrollover
            from data d
            where rn = 1
        union all
        select d.*,
            d.numdaysallowed + c.actualrollover,
            case when d.numdaysallowed + c.actualrollover - d.used > d.maxrollover
                then 3
                else d.numdaysallowed + c.actualrollover - d.used
            end
        from cte c
        inner join data d on d.rn = c.rn + 1 and d.daytype = c.daytype
    )
    select * from cte order by theyear
    

    Demo on DB Fiddle