Generate due dates using function in a select statement

Question

I want to create a semi - monthly amortization schedule by using a function.

Here is my function:

CASE WHEN day(@dt) < 16 
THEN 
   CASE WHEN month(@dt) = 2 -- IF FEBRUARY, THEN GET LAST DAY OF MONTH 
   THEN 
      eomonth(@dt) 
   ELSE -- SET AS 30 
      dateadd(day, 30-day(@dt), @dt) end 
ELSE 
   dateadd(month, 1, dateadd(day, 15-day(@dt), @dt)) -- SET AS 15  
END

Let's say I already have a table of schedule like this

instno |  amount   |  duedate    
------------------------------
1      |  1000.00  |  1-30-2020
2      |  1000.00  |  NULL
3      |  1000.00  |  NULL
4      |  1000.00  |  NULL
5      |  1000.00  |  NULL

I want to fill the remaining 4 installments using the function I created or similar.

So far, I have tried doing,

select *,
    dbo.duedategenerator(LAG(duedate,1,'1-30-2020') OVER (ORDER BY b.ilno)) 
from table

Output should be like this:

instno |  amount   |  duedate    
------------------------------
1      |  1000.00  |  1-30-2020
2      |  1000.00  |  2-15-2020
3      |  1000.00  |  2-29-2020
4      |  1000.00  |  3-15-2020
5      |  1000.00  |  3-30-2020

Paul White · Answer 1 · 2020-04-02T15:52:16.263

One solution uses recursion:

Setup

CREATE TABLE dbo.Test
(
    instno integer PRIMARY KEY,
    amount money NOT NULL,
    duedate date NULL
);
GO
INSERT dbo.Test
    (instno, amount, duedate)
VALUES
    (1, 1000.00, '20200130'),
    (2, 1000.00, NULL),
    (3, 1000.00, NULL),
    (4, 1000.00, NULL),
    (5, 1000.00, NULL);

CREATE FUNCTION dbo.SemiMonthlyAmortizationScheduleDate
    (@dt date)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        ScheduleDate =
            CASE 
                WHEN DAY(@dt) < 16 
                THEN 
                    CASE 
                        WHEN MONTH(@dt) = 2 -- IF FEBRUARY, THEN GET LAST DAY OF MONTH 
                        THEN EOMONTH(@dt) 
                        ELSE DATEADD(DAY, 30 - DAY(@dt), @dt) -- SET AS 30
                    END 
            ELSE 
               DATEADD(MONTH, 1, DATEADD(DAY, 15 - DAY(@dt), @dt)) -- SET AS 15
            END;

Solution

WITH
    R AS
    (
        SELECT
            T.instno,
            T.amount,
            T.duedate
        FROM dbo.Test AS T
        ORDER BY 
            T.instno
            OFFSET 0 ROWS
            FETCH FIRST 1 ROW ONLY

        UNION ALL

        SELECT
            Top1.instno,
            Top1.amount,
            duedate = SD.ScheduleDate
        FROM 
        (
            SELECT
                T.instno,
                T.amount,
                R.duedate,
                rn = ROW_NUMBER() OVER (
                    ORDER BY T.instno ASC)
            FROM R
            JOIN dbo.Test AS T
                ON T.instno > R.instno
        ) AS Top1
        CROSS APPLY dbo.SemiMonthlyAmortizationScheduleDate(Top1.duedate) AS SD
        WHERE
            Top1.rn = 1
    )
SELECT
    R.instno,
    R.amount,
    R.duedate
FROM R
ORDER BY
    R.instno;

Try it online at db<>fiddle demo

The ROW_NUMBER thing is just a way to get TOP in the recursive part of a CTE see Performance Tuning the Whole Query Plan for details on that if you need it.

If you can use SQL CLR, a faster solution is available using the technique I show in my answer to How can I eliminate this costly index seek operation from my query?

Generate due dates using function in a select statement

1 Answers1

Setup

Solution