look ugly
DECLARE @Sample Table(ACCT char(3), CurBal int, [Date] varchar(4))
INSERT INTO @Sample VALUES ('001',100,'day1'), ('002',90,'day1'), ('003',50,'day1'), ('001',80,'day2'), ('003',110,'day2'), ('004',100,'day2')
;WITH CTE AS(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY ACCT ORDER BY [DATE]) RN
FROM @Sample
)
SELECT
a.ACCT,
CASE WHEN a.CurBal <> b.CurBal THEN b.CurBal ELSE a.CurBal END,
b.Date,
CASE WHEN a.CurBal < b.CurBal THEN a.CurBal WHEN b.CurBal < a.CurBal THEN b.CurBal ELSE a.CurBal END,
b.RN - a.RN + 1,
a.Date
FROM CTE a
CTE b
WHERE a.ACCT = b.ACCT
AND a.RN <= b.RN
OUTER APPLY(
SELECT *
FROM CTE b
WHERE a.ACCT = b.ACCT
AND a.RN <= b.RN)b
ORDER BY 3, 6, 1