Next Payment Month using Start Month, Frequency and Current Month

  • I have a table showing assets. A payment file is produced every month showing all assets, regardless of if they need to have paid that month. I have the following fields..

    'File Month' - The monthly date of the file, so 'December 2015'

    'Start Month' - The month the asset started paying

    'Frequency' - How often they pay (options are annually, half yearly, quarterly or monthly)

    I need to work out the next Payment Month. I can get there easily for yearly (add 12 to the Start Month) and monthly(add 1 to the File Month), but stumped at the moment on how to do the quarterly and half yearly payers. I can't just add the frequency to the start month because it's dependent on what month we are in now..

    E.g.

    For December, for those who started paying in April and were half year payers, I'd want their next payment month to be April because they would have already paid in October

    For December, for those who started paying in August and were quarterly payers, I'd want their next payment month to be Feb cause they would have paid in November.

    CREATE TABLE [dbo].[AssetPayments](

    [FileMonth] [int] NULL,

    [AssetId] [int] NULL,

    [StartMonth] [int] NULL,

    [PaymentFrequencyInMonths] [int] NULL

    ) ON [PRIMARY]

    GO

    INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)

    VALUES (12, 1234, 5, 1)

    INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)

    VALUES (12, 5678, 10, 3)

    INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)

    VALUES (12, 1111, 7, 6)

    INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)

    VALUES (12, 2222, 6, 12)

  • -- I've tweaked your table definition so as to use proper dates instead of months numbers

    -- Notice the constraints to keep any dodgy dates out of the table

    CREATE TABLE dbo.AssetPayments(

    FileMonth date NOT NULL CONSTRAINT CK_FileMonth CHECK (DATEPART(DAY,FileMonth) = 1)

    ,AssetId int NOT NULL

    ,StartMonth date NOT NULL CONSTRAINT CK_StartMonth CHECK (DATEPART(DAY,StartMonth) = 1)

    ,PaymentFrequencyInMonths int NOT NULL CONSTRAINT CK_Freq CHECK (PaymentFrequencyInMonths IN (1,3,6,12))

    ) ON [PRIMARY];

    GO

    INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)

    VALUES ('2015-12-01', 1234, '2015-05-01', 1);

    INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)

    VALUES ('2015-12-01', 5678, '2015-10-01', 3);

    INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)

    VALUES ('2015-12-01', 1111, '2015-07-01', 6);

    INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)

    VALUES ('2015-12-01', 2222, '2015-06-01', 12);

    -- I've used a permanent table here, but you can create it on the fly

    -- when you run the query if you prefer

    CREATE TABLE Months (

    MonthStart date NOT NULL CONSTRAINT CK_MonthStart CHECK (DATEPART(DAY,MonthStart) = 1)

    CONSTRAINT PK_Months_MonthStart PRIMARY KEY (MonthStart)

    ) ON [PRIMARY]

    INSERT INTO Months VALUES

    ('2015-01-01'),('2015-02-01'),('2015-03-01'),('2015-04-01'),

    ('2015-05-01'),('2015-06-01'),('2015-07-01'),('2015-08-01'),

    ('2015-09-01'),('2015-10-01'),('2015-11-01'),('2015-12-01'),

    ('2016-01-01'),('2016-02-01'),('2016-03-01'),('2016-04-01'),

    ('2016-05-01'),('2016-06-01'),('2016-07-01'),('2016-08-01'),

    ('2016-09-01'),('2016-10-01'),('2016-11-01'),('2016-12-01')

    SELECT

    a.AssetID

    ,MIN(m.MonthStart) NextMonth

    FROM AssetPayments a

    JOIN Months m

    ON DATEDIFF(month,a.StartMonth,m.MonthStart)%a.PaymentFrequencyInMonths = 0

    WHERE m.MonthStart > CURRENT_TIMESTAMP

    GROUP BY a.AssetId

    John

  • I can't get that piece of code to work. What is the % sign in the join?

  • murtzd (12/22/2015)


    I can't get that piece of code to work.

    Error messages? Unexpected results?

    What is the % sign in the join?

    Please see here. It's so that we only consider months that are an integer multiple of the payment interval after the start date.

    John

  • The % sign returns the remainder of a division. So 10 % 3 = 1

  • This will return the data with the current table design. I agree with John on correcting the data types, but I understand that sometimes is not possible. If at all possible, please change them.

    WITH

    E(n) AS(

    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

    ),

    E2(n) AS(

    SELECT a.n FROM E a, E b

    ),

    cteTally(n) AS(

    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

    FROM E2

    )

    SELECT FileMonth,

    AssetId,

    StartMonth,

    MIN( ISNULL(NULLIF( (StartMonth + (n * p.PaymentFrequencyInMonths)) % 12, 0), 12)) PaymentMonths

    FROM dbo.AssetPayments p

    JOIN cteTally t ON p.PaymentFrequencyInMonths <= 12. / t.n

    WHERE StartMonth + (n * p.PaymentFrequencyInMonths) > 12

    GROUP BY FileMonth,

    AssetId,

    StartMonth

    ORDER BY AssetId;

    To understand what the cteTally is doing, read this: http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks for that, I feel like I'm close with it, however I'm facing a few issues.

    I have the following;

    Filemonth = 10

    StartMonth = 10

    Frequency = 1

    I'm expecting the NextPaymentMonth to be 11 because it has a frequency of 1 (monthly) and the current month is 10, so it should be 11.

    However, the results from your code is showing 1?

  • I'm sorry, change the 12 in the where clause to use file month column.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Still doesn't seem to be working..

    Filemonth = 10

    StartMonth = 12

    Frequency = 6

    I'm expecting NextPaymentMonth to be 12, but your code result is showing 6...

  • If I understand the requirements correctly, something like this should work:

    CREATE TABLE #test (filemonth int, startmonth int, frequency int);

    INSERT INTO #test

    VALUES (5,10,6),

    (5,10,1),

    (5,1,2),

    (5,2,3),

    (5,4,5),

    (5,10,3),

    (5,4,12),

    (10,10,1),

    (10,1,2),

    (10,2,3),

    (10,4,5),

    (10,10,3),

    (10,12,6);

    SELECT *,

    next_payment=CASE WHEN (startmonth+(frequency*((((CASE WHEN filemonth>=startmonth THEN filemonth-startmonth ELSE filemonth+12-startmonth END))/frequency)+1)))%12=0

    THEN 12

    ELSE (startmonth+(frequency*((((CASE WHEN filemonth>=startmonth THEN filemonth-startmonth ELSE filemonth+12-startmonth END))/frequency)+1)))%12

    END

    FROM #test;

    DROP TABLE #test;

    Cheers!

    EDIT: Re-reading this I noticed I posted my first test attempt, which only worked if filemonth>=startmonth. I had fixed this, but posted the first version anyway. I've corrected that, but Doh! 🙂

  • Apologies for the late reply, Christmas holidays!

    Jacob, that solution works absolutely perfectly! It performs with no extra overhead too. Thank you so so much. Happy new year!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply