By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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);
9 rows affected
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
| Deal | RecentOverdueDate |
|---|---|
| 111111 | 2017-12-12 |
| 222221 | 2017-12-20 |
Warning: Null value is eliminated by an aggregate or other SET operation.