SQL Server向后遍历结果集

Question

我的任务是为账户生成欠款报告。

这是相当简单的，除了一个专栏，DaysInArrears。

我已经创建了一个查询，它计算出帐户在每次计划付款时的欠款状态。

此查询使用如下所示的结果填充临时表(#ArrearsAnalysis)：

AgreementID DueDate     AmountDue DueTD  PaidTD FirstPaymentDue ArrearsStatus   RN
184         2013-03-11  75.00     75.00  0.00   2013-03-11      1               1
184         2013-03-25  75.00     150.00 0.00   2013-03-11      1               2
184         2013-04-08  75.00     225.00 300.00 2013-03-11      0               3
184         2013-04-22  75.00     300.00 300.00 2013-03-11      0               4
184         2013-05-03  75.00     375.00 300.00 2013-03-11      1               5
184         2013-05-20  75.00     450.00 300.00 2013-03-11      1               6

我还从下面的查询中填充了另一个临时表(#ArrearsCheck)：

INSERT INTO #ArrearsCheck
SELECT 
    AgreementID, 
    SUM(ArrearsStatus),
    MAX(RN)
FROM 
    #ArrearsAnalysis 
GROUP BY 
    AgreementID

然后，我需要做的是回顾这些行，找出它们何时陷入最新的欠款(欠款分数为1)。

因此，对于上面的示例，我预计结果为03/05/2013。

如果一个帐户从来没有付款，那么我将返回FirstPaymentDue日期。如果一个帐户从未欠费，那么我将返回NULL

到目前为止，我的查询是关于非付款人和非欠费的，我只需要循环的帮助来解决上面的问题(用**突出显示)：

SELECT
    DISTINCT(A.AgreementID),
    CASE WHEN (C.ArrearsTally <= 0) 
    THEN NULL 
    ELSE (CASE WHEN (C.ArrearsTally = C.PaymentCount) 
          THEN NULL 
          *ELSE 0* 
          END) 
    END AS ArrearsDate
FROM
    #ArrearsAnalysis AS A
LEFT OUTER JOIN
    #ArrearsCheck AS C ON A.AgreementID = C.AgreementID

然后，我将把这个查询的结果传递给我的下一个查询，它将计算今天和结果日期之间的差值(天)。

Answer 1

如果我们可以信任RN (行数？)是按顺序编号的，那么我们将查找两个相邻的记录，其中较新的记录具有ArrearsStatus =1，而前一个记录具有ArrearsStatus =0(或者不存在，例如NULL)。我们需要与最近的此类事件相关联的ArrearsStatus =1记录的日期：

SELECT MAX(aa1.DueDate) AS MaxDueDate
FROM #ArrearsAnalysis aa1
LEFT JOIN #ArrearsAnalysis aa2
ON aa2.RN = aa1.RN - 1
WHERE aa1.ArrearsStatus = 1
AND (aa2.ArrearsStatus = 0 OR aa2.ArrearsStatus IS NULL)

如果我们不能信任RN，我们将不得不从基于日期的加入条件开始(在aa2.DueDate < aa1.DueDate)，然后找到其他方法来建立邻接关系。(可能在中间日期进行第三次自联接，然后坚持中间日期为空...)

基于集合的方法通常比循环更好。在这种情况下，只有当数据最终使基于集合的方法不切实际时，我才会使用游标循环。

您可以添加aa1.Agreement add分组。我不认为我们需要#ArrearsCheck。

更新

上面的答案适用于最初被表述为“计算出他们何时陷入最新欠款”的问题。关于“最早的欠款”的问题实际上更有趣一些。我们不能简单地切换到MIN()，因为我们希望仅当AgreementID从一开始就处于欠费状态时才能看到初始DueDate，并且从来没有从0到1的转换。

对于我来说，最直接的解决方案是查找每种情况的MIN()日期，然后选择这两个值中的MAX()。Nulls应从聚合中删除。

SELECT b.AgreementID,
MAX(b.MinDueDate) AS FirstArrearsDate
FROM (
SELECT aa1.AgreementID,
MIN(aa1.DueDate) AS MinDueDate
FROM #ArrearsAnalysis aa1
LEFT JOIN #ArrearsAnalysis aa2
ON aa2.RN = aa1.RN - 1
WHERE aa1.ArrearsStatus = 1
AND aa2.ArrearsStatus = 0
GROUP BY aa1.AgreementID
UNION ALL
SELECT aa1.AgreementID,
MIN(aa1.DueDate) AS MinDueDate
FROM #ArrearsAnalysis aa1
LEFT JOIN #ArrearsAnalysis aa2
ON aa2.RN = aa1.RN - 1
WHERE aa1.ArrearsStatus = 1
AND aa2.ArrearsStatus IS NULL
GROUP BY aa1.AgreementID
) AS b
GROUP BY b.AgreementID