CREATE TABLE #t
(RowNo INT IDENTITY(1, 1),
ID INT,
Debit DECIMAL(10, 2),
Credit DECIMAL(10, 2));
INSERT INTO
#t
VALUES
(13334357, 209.67, 0),
(13334357, 0, 209.67),
(13334357, 1000, 0),
(13334357, 0, 1000),
(64284964, 1500, 0),
(64284964, 0, 2700),
(64284964, 1200, 0);
WITH
CTE AS
(SELECT
*,
RunningTotal = SUM(Debit + (Credit * -1)) OVER (PARTITION BY ID ORDER BY ID ROWS UNBOUNDED PRECEDING)
FROM
#t),
CTE2 AS
(SELECT
*,
CASE
WHEN LAG(RunningTotal) OVER (PARTITION BY ID ORDER BY RowNo) IS NULL THEN 1
WHEN LAG(RunningTotal) OVER (PARTITION BY ID ORDER BY RowNo) = 0 THEN 1
ELSE 0
END AS Indicator
FROM
CTE)
SELECT
ID,
Debit,
Credit,
CHAR(SUM(Indicator) OVER (PARTITION BY ID ORDER BY RowNo) + 64) AS TypeCode
FROM
CTE2;
DROP TABLE #t;
