计算SQL Server 2008中的到期日期
第一期付款日期是加入日期。 客户必须支付10期分期付款。 每个月的每一期付款。 因此，11个月后，客户有资格购买节省的产品。 所以到期日是从加入日起11个月。 示例加入日期：2016年9月12日到期日：2017年7月12日
如果客户在预定日期或当月到期日的任何一天付款不延长，则延长到期日。
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
你的问题不清楚你想要达到的目标。 但是，从我的两个例子中可以看出，到期日应该比分期支付的延迟多数月。
即使你的提琴手方案也不清楚。 有了这个小小的理解，我想出了下面的T-SQL来解决你的问题：
-- 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
上一篇: Calculate Maturity date in SQL Server 2008
下一篇: Distribute amount monthly with respect to start date and end date