在SQL中查找当前债务的日期

Question

请帮我弄清楚如何找出当前债务的日期和自其成立以来的天数，我有这个表:

和20.11.200925000-3000020000-10000120005000-1010010000-10000

在阅读了对这个答案的评论之后，这里有一个解决问题的方法。我采用了稍微冗长的方法，以便您可以遵循逻辑，但可以随意折叠一些常见的表表达式以使其更短。

我们可以计算每个交易的运行SUM，我将为每个交易编号行。然后，我们可以使用LAG比较当前一行交易的SUM与前一行交易的SUM。当SUM由负变为正，或者总和为正，而之前的SUM为NULL时，我们就找到了债务交叉的地方。在这些情况下，我将行号乘以-1，这样我就可以找到每个交易的MIN行号，这将是最近的欠债日期。正如我所提到的，这可以缩短，但我留下了一些冗长，以便您可以遵循逻辑:

;WITH sums AS (
SELECT Deal, 
Payment_Dt, 
SUM(Sum_payment) OVER (PARTITION BY Deal ORDER BY Payment_dt) AS [currentSum], 
ROW_NUMBER() OVER (PARTITION BY Deal ORDER BY Payment_dt)  AS [num]
FROM #PDCL
), sumsWithLag AS (
SELECT Deal, Payment_dt, 
currentSum,  
LAG(currentSum) OVER (PARTITION BY Deal ORDER BY Payment_dt)  AS [prevSum], 
num
FROM sums
), markedCrossings AS (
SELECT Deal, Payment_dt, 
CASE WHEN currentSum > 0 AND (prevSum IS NULL OR prevSum < 0) THEN -1 ELSE 1 END * num AS num
FROM sumsWithLag
), debtCrossings AS (
SELECT Deal, MIN(num) AS num 
FROM markedCrossings 
GROUP BY Deal
)
SELECT s.Deal, s.Payment_dt AS Start_date_current_debt
FROM debtCrossings AS c 
INNER JOIN sums AS s ON s.Deal = c.Deal and s.num = ABS(c.num)

结果如下:

tbody> <<tr>

交易	Start_date_current_debt
111111	2009-12-12
122222	2009-12-12
222221	2009-12-20