sql - 连续的日期

Question

希望这不是另一个问题的骗局，但我在其他任何地方都看不到它 - 这也是 another question I asked 的简化版本，希望能让我开始研究如何处理它。

我希望计算出每个月至少有一次付款的连续付款范围。

我有以下示例数据

CREATE TABLE #data
(
Contact_reference NVARCHAR(55)
,Date_payment DATETIME
,Payment_value MONEY
)

INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2003-06-08',12.82)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2004-06-08',12.82)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2004-12-08',12.82)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-04-08',12.82)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-05-08',12.82)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-06-08',12.82)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-07-08',12.82)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-08-08',12.82)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-09-08',12.82)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-10-10',12.8205)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-11-10',12.8205)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-12-10',12.8205)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2006-01-10',12.8205)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2006-02-10',12.8205)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2006-02-28',12.8205)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2006-04-12',12.8205)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2006-05-10',19.2308)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-06-11',19.2308)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-07-10',19.2308)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-08-09',19.2308)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-09-10',19.2308)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-10-09',19.2308)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-11-09',19.2308)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-12-10',19.2308)
INSERT INTO #data VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2008-01-10',19.2308)

我希望能够为每个联系人计算出他们连续给予的范围(定义为每个日历月至少给予一次)、连续付款的次数、每次的总值(value)范围(理想情况下，如果可能的话，当前范围与最近范围末尾之间的差距)。

对于上面的测试数据，我的输出将如下所示:

CREATE TABLE #results
(
contact_reference NVARCHAR(55)
,Range_start DATETIME
,Range_end DATETIME
,Payments INT
,Value MONEY
,months_until_next_payment INT --works out the gap between the range_end date for a group and the range_start date for the next group
)
INSERT INTO #results VALUES('18EC3CD2-3065-4FF4-BE40-000004228590','2003-06-08','2003-06-08',1,12.82,12)
INSERT INTO #results VALUES('18EC3CD2-3065-4FF4-BE40-000004228590','2004-06-08','2004-06-08',1,12.82,6)
INSERT INTO #results VALUES('18EC3CD2-3065-4FF4-BE40-000004228590','2004-12-08','2004-12-08',1,12.82,4)
INSERT INTO #results VALUES('18EC3CD2-3065-4FF4-BE40-000004228590','2005-04-08','2006-02-28',12,153.843,2)
INSERT INTO #results VALUES('18EC3CD2-3065-4FF4-BE40-000004228590','2006-04-12','2008-06-06',27,416.6673,NULL)

我一直在寻找使用孤岛或迭代的答案，但坦率地说，我什至不知道从哪里开始将它们应用于我的问题，所以非常感谢任何帮助:)

Answer 1

编辑:我已在 months_until_next_payment 列中添加。这在应用程序中比使用自连接更有效，但是因为 SQL Server 没有任何特别令人满意的引用下一行和上一行的方法。

;WITH base AS ( 
SELECT    Contact_reference  ,
          Payment_value,
          DATEPART(YEAR, Date_payment)*12 + DATEPART(MONTH, Date_payment) - 
               DENSE_RANK() OVER 
                   (PARTITION BY Contact_reference 
                    ORDER BY DATEPART(YEAR, Date_payment)*12 + DATEPART(MONTH, Date_payment)) AS G,
          Date_payment
 FROM     #data
 ),
 cte AS
 (
 SELECT 
          Contact_reference, 
          ROW_NUMBER() over (partition by Contact_reference 
                                 order by MIN(Date_payment)) RN,
          MIN(Date_payment) Range_start,
          MAX(Date_payment) Range_end, 
          COUNT(Payment_value) Payments, 
          SUM(Payment_value) Value
 FROM base
 GROUP BY Contact_reference, G
 )
 SELECT 
       c1.Contact_reference, 
       c1.Payments, 
       c1.Range_end, 
       c1.Range_start, 
       c1.Value, 
       DATEDIFF(month, c1.Range_end,c2.Range_start) months_until_next_payment
 FROM cte c1
 LEFT join cte c2 ON c1.Contact_reference=c2.Contact_reference and c2.RN = c1.RN+1