SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--Create the input data set
DROP TABLE IF EXISTS #InputData;
CREATE TABLE #InputData(Id INTEGER NOT NULL
								, Date DATE NOT NULL
								, Type VARCHAR(7) NOT NULL
								, Amount BIGINT NOT NULL
								);
INSERT INTO #InputData
SELECT X.Id
	, CONVERT(DATE, X.Date, 105) --Converting from VARCHAR to DATE
	, X.Type
	, X.Amount
FROM (
	VALUES(1, '26-01-2024', 'Inflow', 519)
		, (2, '26-01-2024', 'Outflow', 100)
		, (3, '26-01-2024', 'Outflow', 139)
		, (4, '26-01-2024', 'Outflow', 122)
		, (5, '29-01-2024', 'Outflow', 42)
		, (6, '29-01-2024', 'Inflow', 713)
		, (7, '29-01-2024', 'Inflow', 887)
		, (8, '29-01-2024', 'Outflow', 92)
		, (9, '29-01-2024', 'Outflow', 1593)
		, (10, '29-01-2024', 'Outflow', 25)
	) X(Id, Date, Type, Amount);

SELECT * FROM #InputData;


Id
Date
Type
Amount


1
2024-01-26
Inflow
519

2
2024-01-26
Outflow
100

3
2024-01-26
Outflow
139

4
2024-01-26
Outflow
122

5
2024-01-29
Outflow
42

6
2024-01-29
Inflow
713

7
2024-01-29
Inflow
887

8
2024-01-29
Outflow
92

9
2024-01-29
Outflow
1593

10
2024-01-29
Outflow
25


--Need to iterate through this from the last inflow to the first.
--Declare variables used throughout
DECLARE @max_outflow_id BIGINT;
DECLARE @current_inflow_id BIGINT;
DECLARE @current_inflow_amount BIGINT;
DECLARE @smallest_processed_outflow_id BIGINT;
DECLARE @current_inflow_count BIGINT;
DECLARE @running_outflow_total BIGINT;
DECLARE @excess_amount BIGINT;
DECLARE @upper_outflow_id BIGINT;
DECLARE @lower_outflow_id BIGINT;
DECLARE @final_outflow_id BIGINT;
DECLARE @next_insert_lower_outflow_id BIGINT;
DECLARE @amount BIGINT;
DECLARE @id BIGINT;

--Create tables used throughout
DROP TABLE IF EXISTS #Inflows
CREATE TABLE #Inflows(id BIGINT NOT NULL
					, amount BIGINT NOT NULL
					, iteration_order BIGINT NOT NULL
					);
--This table will store the results
DROP TABLE IF EXISTS #InflowOutflowMapping
CREATE TABLE #InflowOutflowMapping (inflow_id BIGINT NOT NULL
									, lower_outflow_id BIGINT NOT NULL
									, upper_outflow_id BIGINT NOT NULL
									);

--Get the maximum id in the table associated with an Outflow
SELECT @max_outflow_id = MAX(id) 
FROM #InputData 
WHERE type = 'Outflow';

--Keep a list of the inflow Ids from last to first, as well as their amounts.
INSERT INTO #Inflows(id
				, amount
				, iteration_order
				)
SELECT id
	, amount
	, ROW_NUMBER() OVER(ORDER BY id DESC) AS iteration_order
FROM #InputData 
WHERE type = 'Inflow'
ORDER BY iteration_order ASC;

--Get the first inflow id to be processed
SELECT @current_inflow_id = id 
	, @current_inflow_amount = Amount
FROM #Inflows
WHERE iteration_order = 1;

SET @current_inflow_count = 1;

--Get the lower and upper bound for the outflow ids which will be considered first
SELECT @lower_outflow_id = id + 1 --This only works due to careful construction
FROM #Inflows
WHERE iteration_order = 1;
SET @upper_outflow_id = @max_outflow_id;

--Get the outflow id that corresponds to the inflow id that will be processed last.
SELECT @final_outflow_id = MAX(id)
FROM #InputData
WHERE id < (SELECT Id 
			FROM #Inflows
			WHERE iteration_order = (SELECT MAX(iteration_order) - 1
									FROM #Inflows)
			)
	AND type = 'Outflow';
--Cater for the edge case where there is only one inflow
SELECT @final_outflow_id = COALESCE(@final_outflow_id, MAX(id))
FROM #InputData
WHERE type = 'Outflow'

--Keep a tally of the running total of outflows that offset against an inflow
SET @running_outflow_total = 0;

WHILE @upper_outflow_id IS NOT NULL
BEGIN
	--Build a cursor that will be used like a FOR loop
	DECLARE outflow_window CURSOR FOR
	SELECT id
		, amount
	FROM #InputData 
	WHERE id BETWEEN @lower_outflow_id AND @upper_outflow_id 
		AND type = 'Outflow'
	ORDER BY id ASC;
	
	--Active the cursor, just some t-sql syntax.
	OPEN outflow_window;
	--Take the first record from the cursor, and assign local variables from it.
	FETCH NEXT FROM outflow_window
	INTO @id
		, @amount;
	--Need to keep track of the lower bound outflow_id for the next insert operation.
	SET @next_insert_lower_outflow_id = @lower_outflow_id;
	
	WHILE @@FETCH_STATUS = 0 --Think of this as a FOR loop over the outflow_window cursor
	BEGIN
		--Have we exceeded the current inflow amount with our tallied outflows?
		IF @running_outflow_total + @amount >= @current_inflow_amount
		BEGIN
			--Inflow has been consumed, store the result
			INSERT INTO #InflowOutflowMapping (inflow_id
											, lower_outflow_id
											, upper_outflow_id
											)
			VALUES(@current_inflow_id
				, @next_insert_lower_outflow_id
				, @id
				);
			SET @next_insert_lower_outflow_id = @id
			--By how much have we exceeded it?
			SET @excess_amount = @running_outflow_total + @amount - @current_inflow_amount;
			--Reset running total of outflow amount
			SET @running_outflow_total = 0;
			--Let's get ready to move onto the next inflow
			SET @current_inflow_count = @current_inflow_count + 1;
			--The excess amount may contribute to multiple inflows (if it exceeds each inflows amount)
			WHILE @excess_amount > 0
			BEGIN
				--Get the next inflow id, and the amount associated with it
				SELECT @current_inflow_id = id
					, @current_inflow_amount = amount
				FROM #Inflows
				WHERE iteration_order = @current_inflow_count;
				--if the excess exceeds the current_inflow_amount, then there is some new excess leftover.
				IF @excess_amount >= @current_inflow_amount
				BEGIN
					--Inflow has been consumed (by a single outflow), store the result
					INSERT INTO #InflowOutflowMapping (inflow_id
													, lower_outflow_id
													, upper_outflow_id
													)
					VALUES(@current_inflow_id
						, @next_insert_lower_outflow_id
						, @next_insert_lower_outflow_id
						);
					--Calculate the new excess.
					SET @excess_amount = @excess_amount - @current_inflow_amount;
					--Let's get ready to move onto the next inflow
					SET @current_inflow_count = @current_inflow_count + 1;
				END;
				ELSE --Otherwise, the excess is smaller than the @current_inflow_amount
				BEGIN
					--Set the outflow total to the excess
					SET @running_outflow_total = @excess_amount;
					--Excess is now back to 0. forces an exit on the inner while loop
					SET @excess_amount = 0;
				END;
			END;
		END;
		ELSE -- inflow amount is not exceeded
		BEGIN
			--Increment the running total of outflow amounts consumed.
			SET @running_outflow_total = @running_outflow_total + @amount;
		END;
		
		--Assigning values for the next loop iteration
		FETCH NEXT FROM outflow_window 
		INTO @id
			, @amount 
	END;
	--End of the inner most for loop, need to attribute any remaining outflows to the current inflow
	INSERT INTO #InflowOutflowMapping (inflow_id
										, lower_outflow_id
										, upper_outflow_id
										)
	VALUES(@current_inflow_id
		, @next_insert_lower_outflow_id
		, @upper_outflow_id
		);
	
	--Just some t-sql syntax for closing and removing the cursor. (so we can declare it again with no issues)
	CLOSE outflow_window;
	DEALLOCATE outflow_window;
	
	--We've reached the end of the window of outflows we just looped through.
	--Need to now look at all outflows (that we haven't already seen) since the current inflow 
	SELECT @upper_outflow_id = MAX(id)
	FROM #InputData
	WHERE id < @lower_outflow_id --When this returns nothing, @upper_outflow_id will be NULL and the outer-most WHILE will terminate
		AND type = 'Outflow';
		
	SELECT @lower_outflow_id = MIN(id)
	FROM #InputData
	WHERE id > @current_inflow_id 
		AND type = 'Outflow';
END;
7 rows affected

SELECT *
FROM #InflowOutflowMapping;

SELECT ind.*
	, iom.inflow_id
FROM #InputData ind
LEFT JOIN #InflowOutflowMapping iom
	ON ind.id BETWEEN iom.lower_outflow_id AND iom.upper_outflow_id
    AND ind.type = 'Outflow'
ORDER BY ind.id ASC
;


inflow_id
lower_outflow_id
upper_outflow_id


7
8
9

6
9
9

1
9
10

1
2
5




Id
Date
Type
Amount
inflow_id


1
2024-01-26
Inflow
519
null

2
2024-01-26
Outflow
100
1

3
2024-01-26
Outflow
139
1

4
2024-01-26
Outflow
122
1

5
2024-01-29
Outflow
42
1

6
2024-01-29
Inflow
713
null

7
2024-01-29
Inflow
887
null

8
2024-01-29
Outflow
92
7

9
2024-01-29
Outflow
1593
7

9
2024-01-29
Outflow
1593
6

9
2024-01-29
Outflow
1593
1

10
2024-01-29
Outflow
25
1
Id	Date	Type	Amount
1	2024-01-26	Inflow	519
2	2024-01-26	Outflow	100
3	2024-01-26	Outflow	139
4	2024-01-26	Outflow	122
5	2024-01-29	Outflow	42
6	2024-01-29	Inflow	713
7	2024-01-29	Inflow	887
8	2024-01-29	Outflow	92
9	2024-01-29	Outflow	1593
10	2024-01-29	Outflow	25
Id	Date	Type	Amount	inflow_id
1	2024-01-26	Inflow	519	null
2	2024-01-26	Outflow	100	1
3	2024-01-26	Outflow	139	1
4	2024-01-26	Outflow	122	1
5	2024-01-29	Outflow	42	1
6	2024-01-29	Inflow	713	null
7	2024-01-29	Inflow	887	null
8	2024-01-29	Outflow	92	7
9	2024-01-29	Outflow	1593	7
9	2024-01-29	Outflow	1593	6
9	2024-01-29	Outflow	1593	1
10	2024-01-29	Outflow	25	1