Calculate Maturity date in SQL Server 2008

Question

1st installment date is Joining Date. Customer has to pay 10 installments. Each installment every month. So after 11 month customer is eligible to buy product for the saved amount. So maturity date is 11 months from the joining date. Example Joining Date: 12/Sep/2016 Maturity Date: 12/Jul/2017

If Customer pays within scheduled date or any day in that month maturity date do not extend else the maturity date is extended.

Scenario 1: Pays on time
------------------------

Joining date: 12/Sep/2016
Expected output: 12/Jul/2017

    Joining Date    12/Sep/2016 

Inst No  Payment Date   Actual Date payment Considered Month
1        12/Sep/2016    12/Sep/2016  Sep 2016
2        05/Oct/2016    12/Oct/2016  Oct 2016
3        21/Nov/2016    12/Nov/2016  Nov 2016
4        12/Dec/2016    12/Dec/2016  Dec 2016
5        02/Jan/2017    12/Jan/2017  Jan 2017
6        02/Feb/2017    12/Feb/2017  Feb 2017
7        06/Mar/2017    12/Mar/2017  Mar 2017
8        06/Apr/2017    12/Apr/2017  Apr 2017
9        01/May/2017    12/May/2017  May 2017
10       07/Jun/2017    12/Jun/2017  Jun 2017

        Maturity Date   12/Jul/2017

Scenario1

Scenario 2: when payments paid with delay
-----------------------------------------

Joining date: 12/Sep/2016
Expected output: 12/Sep/2017

Scenario2

   Joining Date    12/Sep/2016 

Inst No Payment Date    Actual Date Payment Considered Month
1       12/Sep/2016     12/Sep/2016  Sep 2016
2       05/Oct/2016     12/Oct/2016  Oct 2016
3       21/Nov/2016     12/Nov/2016  Nov 2016
4       02/Feb/2017     12/Dec/2016  Feb 2017
5       02/Feb/2017     12/Jan/2017  Mar 2017
6       06/Mar/2017     12/Feb/2017  Apr 2017
7       06/Mar/2017     12/Mar/2017  May 2017
8       01/Jun/2017     12/Apr/2017  Jun 2017
9       01/Jun/2017     12/May/2017  Jul 2017
10      07/Aug/2017     12/Jun/2017  Aug 2017

        Maturity Date   12/Sep/2017

Posting SQL with a table structure and the sample data will help us get a solution, a more detailed description of the rules will help, from what i can see the rule appears to be for every month of missed payment extend by one month. — RegBes

Nagesh Nagesh · Accepted Answer · 2017-10-27T05:07:04

Your question is unclear of what you are trying to achieve. However, what I understood from two of your examples is that the maturity date should be pushed by as many months as the delay in paying installments.

Even your Fiddler scenarios are unclear. With this little understanding, I came up with the following T-SQL to solve your question:

-- Schema generation
CREATE TABLE [dbo].[Payment](
    [InsNo] [int] PRIMARY KEY NOT NULL,
    [ReceiptDate] [datetime] NULL,
    [ScheduledDate] [datetime] NULL,
    [ConsideredMonth] DATETIME NULL,
) ON [PRIMARY]

GO

INSERT INTO [Payment]([InsNo], [ReceiptDate], [ScheduledDate]) 
    VALUES(1, '2016-09-12 00:00:00', '2016-09-12 00:00:00');

INSERT INTO [Payment]([InsNo], [ReceiptDate], [ScheduledDate]) 
    VALUES(2, '2016-10-05 00:00:00', '2016-10-12 00:00:00');

INSERT INTO [Payment]([InsNo], [ReceiptDate], [ScheduledDate]) 
    VALUES(3, '2016-11-21 00:00:00', '2016-11-12 00:00:00');

INSERT INTO [Payment]([InsNo], [ReceiptDate], [ScheduledDate]) 
    VALUES(4, '2017-02-02 00:00:00', '2016-12-12 00:00:00');

INSERT INTO [Payment]([InsNo], [ReceiptDate], [ScheduledDate]) 
    VALUES(5, '2017-02-02 00:00:00', '2016-01-12 00:00:00');

INSERT INTO [Payment]([InsNo], [ReceiptDate], [ScheduledDate]) 
    VALUES(6, '2017-02-02 00:00:00', '2016-02-12 00:00:00');

INSERT INTO [Payment]([InsNo], [ReceiptDate], [ScheduledDate]) 
    VALUES(7, '2017-03-06 00:00:00', '2016-03-12 00:00:00');

INSERT INTO [Payment]([InsNo], [ReceiptDate], [ScheduledDate]) 
    VALUES(8, '2017-03-06 00:00:00', '2016-04-12 00:00:00');

INSERT INTO [Payment]([InsNo], [ReceiptDate], [ScheduledDate]) 
    VALUES(9, '2017-06-01 00:00:00', '2016-05-12 00:00:00');

INSERT INTO [Payment]([InsNo], [ReceiptDate], [ScheduledDate]) 
    VALUES(10, '2017-06-01 00:00:00', '2016-06-12 00:00:00');

GO

--   Solution using Cursor          
DECLARE @receiptDate DATETIME
        ,@lastInstForDate DATETIME
DECLARE @insNo INT

DECLARE _paymentsCursor CURSOR FAST_FORWARD
FOR
    SELECT p.InsNo
            ,p.ReceiptDate
    FROM   Payment p
    ORDER BY
            p.InsNo

OPEN _paymentsCursor

FETCH NEXT FROM _paymentsCursor INTO
    @insNo, @receiptDate
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @lastInstForDate = p.ConsideredMonth
    FROM   Payment p
    WHERE  p.InsNo = @insNo - 1

    IF DATEADD(MONTH ,1 ,@lastInstForDate) > @receiptDate
        UPDATE Payment
        SET ConsideredMonth      = DATEADD(MONTH ,1 ,@lastInstForDate)
        WHERE InsNo = @insNo
    ELSE
        UPDATE Payment
        SET    ConsideredMonth = CAST(CAST(YEAR(ReceiptDate) AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(ReceiptDate) AS VARCHAR(2)) ,2) + '01' AS DATETIME)
        WHERE  InsNo = @insNo

    FETCH NEXT FROM _paymentsCursor INTO
        @insNo, @receiptDate
END

CLOSE _paymentsCursor
DEALLOCATE _paymentsCursor


DECLARE @lastPaymentDate DATETIME

DECLARE @maturityDate DATETIME
SELECT @maturityDate = DATEADD(month, 1, MAX(p.ConsideredMonth)), @lastPaymentDate = MAX(p.ReceiptDate) FROM dbo.Payment p
SET @maturityDate = CAST(CAST(YEAR(@maturityDate) AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(@maturityDate) AS VARCHAR(2)) ,2) + RIGHT('0' + CAST(DAY(@lastPaymentDate) AS VARCHAR(2)) ,2) AS DATETIME)
SET @maturityDate = DATEADD(DAY, 1, @maturityDate)

SELECT @maturityDate

Calculate Maturity date in SQL Server 2008

3 Answers