Splitting record data dependent on other table data

Question

Hi,

Hoping for some assistance.

I have a table which holds how much consumer owe in two different buckets and there is another table where the transactions are held.

I need to split the transactions based on how much consumer is owe in those buckets but in recursive fashion.

Please see below inputs and expected output:

DROP TABLE IF EXISTS #Consumer1;
Create table #Consumer1(ConsumerID INT, Fee decimal(18,2), Arrears decimal(18,2));
INSERT INTO #Consumer1
SELECT 1001,10,2 UNION ALL
SELECT 1002,10,0 UNION ALL
SELECT 1003,10,12;

DROP TABLE IF EXISTS #Trans1;
Create table #Trans1(ConsumerID INT, Transamount decimal(18,2));
INSERT INTO #Trans1
SELECT 1001,9 UNION ALL
SELECT 1001,4 UNION ALL
SELECT 1001,3 UNION ALL
SELECT 1002,15 UNION ALL
SELECT 1003,20 UNION ALL
SELECT 1003,1 UNION ALL
SELECT 1003,5;


SELECT 1001 ConsumerID,9 Transamount,9 FeeAllocation,0 ArrearsAllocation UNION ALL
SELECT 1001,4,2,2 UNION ALL
SELECT 1001,3,3,0 UNION ALL
SELECT 1002,15,15,0 UNION ALL
SELECT 1003,20,10,10 UNION ALL
SELECT 1003,1,0,1 UNION ALL
SELECT 1003,5,4,1

Thanks,

Eshwar.

Answer 1

I think that the Transactions table requires an additional column for ordering, something like this:

Create table #Trans1(ConsumerID INT, Transamount decimal(18,2), OrdNum int)
INSERT INTO #Trans1
SELECT 1001,9,1 UNION ALL
SELECT 1001,4,2 UNION ALL
SELECT 1001,3,3 UNION ALL
SELECT 1002,15,1 UNION ALL
SELECT 1003,20,1 UNION ALL
SELECT 1003,1,2 UNION ALL
SELECT 1003,5,3

Then the long solution is:

;
with Q1 as
(
	select ConsumerID, Fee, Arrears, Transamount = cast(0 as decimal(18,2)), FeeAllocation = cast(0 as decimal(18,2)), ArrearsAllocation = cast(0 as decimal(18,2)), i = 0
	from #Consumer1
	union all
	select q.ConsumerID, 
		Fee = cast( Fee - least(q.Fee, t.Transamount) + t.Transamount - least(q.Fee, t.Transamount) - least(q.Arrears, t.Transamount - least(q.Fee, t.Transamount)) as decimal(18,2)),
		Arrears = cast( Arrears - least(q.Arrears, t.Transamount - least(q.Fee, t.Transamount)) as decimal(18,2)),
		Transamount = cast( t.Transamount as decimal(18,2)),
		FeeAllocation = cast( least(q.Fee, t.Transamount) + t.Transamount - least(q.Fee, t.Transamount) - least(q.Arrears, t.Transamount - least(q.Fee, t.Transamount)) as decimal(18,2)),
		ArrearsAllocation = cast( least(q.Arrears, t.Transamount - least(q.Fee, t.Transamount)) as decimal(18,2)),
		i = i + 1
	from Q1 q
	inner join #Trans1 t on t.ConsumerID = q.ConsumerID and t.OrdNum = q.i + 1
)
select ConsumerID, Transamount, FeeAllocation, ArrearsAllocation
from Q1 q
where i <> 0
order by ConsumerID, i
option (maxrecursion 0)