sql - 查找最近的逾期日期

Question

我有以下问题:我需要从工资和会费表中找到最后一次逾期的日期。例如，这是表格和数据:

create table t (
    Id int
  , [date] date
  , Customer varchar(6)
  , Deal varchar(6)
  , Currency varchar(3)
  , [Sum] int
);

insert into t values
  (1, '2017-12-12', '1110', '111111', 'USD', 12000)
, (2, '2017-12-25', '1110', '111111', 'USD', 5000)
, (3, '2017-12-13', '1110', '122222', 'USD', 10000)
, (4, '2018-01-13', '1110', '111111', 'USD', -10100)
, (5, '2017-11-20', '2200', '222221', 'USD', 25000)
, (6, '2017-12-20', '2200', '222221', 'USD', 20000)
, (7, '2017-12-31', '2201', '222221', 'USD', -10000)
, (8, '2017-12-29', '1110', '122222', 'USD', -10000)
, (9, '2017-11-28', '2201', '222221', 'USD', -30000);

如果“Sum”的值为正——表示逾期开始；如果“总和”为负 - 表示有人为此交易付款。

在上面的交易“122222”示例中，逾期从 2017 年 12 月 13 日开始到 2017 年 12 月 29 日结束，因此它不应该出现在结果中。

对于交易“222221”，从 2017 年 11 月 20 日开始的第一个逾期 25000 已在 2017 年 11 月 28 日完全支付，因此当前逾期的最后日期(我们感兴趣)是 2017 年 12 月- 31

我做出这个选择来总结所有付款，并停留在这里:(

WITH cte AS (
    SELECT *,
            SUM([Sum]) OVER(PARTITION BY Deal ORDER BY [Date]) AS Debt_balance
    FROM t
)

显然，如果没有 0 或负的 Debt_balance，我需要找到(对于每笔交易)最少的日期，否则最后一个 0 余额之后的下一个日期..

对于有关该主题的任何提示和想法，我们将不胜感激。谢谢!

更新我的解决方案版本:

WITH cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY Deal, [Date]) id,
           Deal, [Date], [Sum], 
           SUM([Sum]) OVER(PARTITION BY Deal ORDER BY [Date]) AS Debt_balance
    FROM t
)
SELECT  a.Deal, 
        SUM(a.Sum) AS NET_Debt, 
        isnull(max(b.date), min(a.date)), 
        datediff(day, isnull(max(b.date), min(a.date)), getdate())
FROM cte as a 
LEFT OUTER JOIN cte AS b
ON a.Deal = b.Deal AND a.Debt_balance <= 0 AND b.Id=a.Id+1
GROUP BY a.Deal
HAVING SUM(a.Sum) > 0

Answer 1

我相信您正在尝试使用运行总和并跟踪它何时变为正值，并且它可以多次变为正值并且您想要它变为正值的最后日期。除了运行总和之外，您还需要 LAG():

WITH cte1 AS (
    -- running balance column
    SELECT *
         , SUM([Sum]) OVER (PARTITION BY Deal ORDER BY [Date], Id) AS RunningBalance
    FROM t
), cte2 AS (
    -- overdue begun column - set whenever running balance changes from l.t.e. zero to g.t. zero
    SELECT *
         , CASE WHEN LAG(RunningBalance, 1, 0) OVER (PARTITION BY Deal ORDER BY [Date], Id) <= 0 AND RunningBalance > 0 THEN 1 END AS OverdueBegun
    FROM cte1
)
-- eliminate groups that are paid i.e. sum = 0
SELECT Deal, MAX(CASE WHEN OverdueBegun = 1 THEN [Date] END) AS RecentOverdueDate
FROM cte2
GROUP BY Deal
HAVING SUM([Sum]) <> 0

Demo on db<>fiddle