Calculate Maturity date in SQL Server 2008
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
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
尝试这个 -
SELECT MATURITY_DATE = CASE WHEN MAX([ReceiptDate]) <= DATEADD (MM , 9, '2016-09-12 00:00:00')
THEN DATEADD (MM , 10, '2016-09-12 00:00:00')
ELSE DATEADD (MM , 2, MAX([ReceiptDate])) END
FROM [dbo].[Payment];
你可以使用这个公式。
select DATEADD(DAY,
DATEPART(DAY,(MIN([Actual Date]))) - DATEPART(DAY,(MAX([Payment Date]))),
DATEADD(MONTH,
( 11 - COUNT(*) )
, MAX([Payment Date])) )
from @InstalmentTable