spread payment amounts into invoices, respecting payment types, payment sequencies and payment amounts done != expected payment amounts

Question

Hello,

Shame, but it is third day I'm looking for a solution; hope somebody will shed some light.
Got two tables (SQL server 2017) related to sale, in first there is instalment shema (each invoice is divided into instalments), in second there are payment details (there are several payment methods I have to take into account). Only key between both is invoice number. Tables looks like below:

invoice     operation_serial low_border    up_border     payment_type
----------- ---------------- ------------- ------------- ------------
7349971     8917412          0             0,8           T
7349971     9245806          0,8           286,9         B
(2 rows affected)



invoice     instalment_id total_instalment_number invoice_with_instalment_id current_instalment_price running_sum_price      invoice_total
----------- ------------- ----------------------- -------------------------- ------------------------ ---------------------- ----------------------
7349971     1             12                      73499711                   12,45                    12,45                  286,9
7349971     2             12                      73499712                   24,95                    37,4                   286,9
7349971     3             12                      73499713                   24,95                    62,35                  286,9
7349971     4             12                      73499714                   24,95                    87,3                   286,9
7349971     5             12                      73499715                   24,95                    112,25                 286,9
7349971     6             12                      73499716                   24,95                    137,2                  286,9
7349971     7             12                      73499717                   24,95                    162,15                 286,9
7349971     8             12                      73499718                   24,95                    187,1                  286,9
7349971     9             12                      73499719                   24,95                    212,05                 286,9
7349971     10            12                      734997110                  24,95                    237                    286,9
7349971     11            12                      734997111                  24,95                    261,95                 286,9
7349971     12            12                      734997112                  24,95                    286,9                  286,9
(12 rows affected)

And problem - I need to spread payments into instalments ('fill' instalments with payments) according to payment sequence and amount. For case above both tables should be joined in the way to have - as a result - two rows with instalment_id = 1 and two payment methods (0.8 for T and 12.45 - 0.8 for B, and all other rows should have only one payment type 'B' up to the moment when amount 286.9 will be 'used' to fill all remaining instalments. So in other words - paymnets should, according to theis sequence, 'fill' one by one each instalment (1st with / up to 12.45, then 2nd with / up to 24.95 etc.)

Sounds easy, a? 🙂 but as I said, third day on it, no success. Also issue is that method to achieve goal should be able to manage other cases, like for example first payment (here: 0.😎 may be different, exceeding amount of first instalment (here: 12.45).

If anyone would be able to say 'I know the case, did in in the past' - I would appreciate hints. On the other hand - I would appreciate not giving hints like '..maybe try (for example) using running sums..', without being sure that such advice will work. help 🙂

CREATE TABLE _payments(
 [invoice] [bigint] NOT NULL,
 [operation_serial] [bigint] NOT NULL,
 [low_border] [float] NOT NULL,
 [up_border] [float] NOT NULL,
 [payment_type] [char] NOT NULL
) ON [PRIMARY]

CREATE TABLE _instalments(
 [invoice] [int] NOT NULL,
 [instalment_id] [bigint] NOT NULL,
 [total_instalment_number] [bigint] NOT NULL,
 [invoice_with_instalment_id] [bigint] NOT NULL,
 [current_instalment_price] [float] NOT NULL,
 [running_sum_price] [float] NOT NULL,
 [invoice_total] [float] NOT NULL,
) ON [PRIMARY]


insert into _payments values (7349971, 8917412, 0.0, 0.8, 'T')
insert into _payments values (7349971, 9245806, 0.8, 286.9, 'B')

insert into _instalments values ( 7349971, 1,  12, 73499711,  12.45, 12.45, 286.9 )
insert into _instalments values ( 7349971, 2,  12, 73499712,  24.95, 37.4, 286.9 )
insert into _instalments values ( 7349971, 3,  12, 73499713,  24.95, 62.35, 286.9 ) 
insert into _instalments values ( 7349971, 4,  12, 73499714,  24.95, 87.3, 286.9 )
insert into _instalments values ( 7349971, 5,  12, 73499715,  24.95, 112.25, 286.9 )
insert into _instalments values ( 7349971, 6,  12, 73499716,  24.95, 137.2, 286.9 ) 
insert into _instalments values ( 7349971, 7,  12, 73499717,  24.95, 162.15, 286.9 )
insert into _instalments values ( 7349971, 8,  12, 73499718,  24.95, 187.1, 286.9 ) 
insert into _instalments values ( 7349971, 9,  12, 73499719,  24.95, 212.05, 286.9 )
insert into _instalments values ( 7349971, 10, 12, 734997110, 24.95, 237, 286.9 ) 
insert into _instalments values ( 7349971, 11, 12, 734997111, 24.95, 261.95, 286.9 )
insert into _instalments values ( 7349971, 12, 12, 734997112, 24.95, 286.9, 286.9 )

regrds
Tomasz

Answer 1

Hi,

As I asked in the comment, more data and expected result can help to clarify the scenario and your needs.

I am not sure that I covered all cases but I think that this query is what you need or at least the base of what you need (we might need to cover more cases in the lase CASE statement once I will be sure that |I am on the right track - meaning that I understood what you need

Please check the query and if this does not cover all cases and you do not succeed the use it for the final query (please try since it should provide all the tricks that you need) then please re-read my comment and add the missing information

;With MyCTE as (  
 SELECT   
 [invoice],[instalment_id],  
 [current_instalment_price],  
 [running_sum_price] = SUM ([current_instalment_price]) OVER (PARTITION BY [invoice] ORDER BY [instalment_id] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),  
 [previous_running_sum_price] = ISNULL(SUM ([current_instalment_price]) OVER (PARTITION BY [invoice] ORDER BY [instalment_id] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0)  
 from _instalments  
)  
,MyCTE2 as (  
 SELECT   
 t1.[invoice],t1.[instalment_id],t1.[current_instalment_price],t1.[running_sum_price], t1.[previous_running_sum_price],   
 t2.low_border, t2.up_border, t2.operation_serial, t2.payment_type  
 FROM MyCTE t1  
 LEFT JOIN _payments t2   
 ON t1.[invoice] = t2.[invoice]   
 AND t1.[running_sum_price] >= t2.low_border and t1.previous_running_sum_price <= t2.up_border  
)  
SELECT   
 t1.[invoice],t1.[instalment_id],t1.[current_instalment_price],t1.[running_sum_price], t1.[previous_running_sum_price],  
 t1.operation_serial, t1.low_border, t1.up_border,t1.payment_type,  
 The_Paymet = --t1.[running_sum_price] - t1.up_border  
 CASE  
 WHEN t1.[running_sum_price] >  t1.up_border THEN t1.up_border  
 WHEN t1.[running_sum_price] <= t1.up_border THEN t1.[running_sum_price] - t1.low_border  
 END  
FROM MyCTE2 t1  

Check the last column in the result which provide the solution

Or maybe this is what you need (as I said it is only playing with the lase CASE stetement to cover all cases and to final adjasment to what exactly you need)

;With MyCTE as (  
	SELECT   
		[invoice],[instalment_id],  
		[current_instalment_price],  
		[running_sum_price] = SUM ([current_instalment_price]) OVER (PARTITION BY [invoice] ORDER BY [instalment_id] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),  
		[previous_running_sum_price] = ISNULL(SUM ([current_instalment_price]) OVER (PARTITION BY [invoice] ORDER BY [instalment_id] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0)  
	from _instalments  
)  
,MyCTE2 as (  
	SELECT   
		t1.[invoice],t1.[instalment_id],t1.[current_instalment_price],t1.[running_sum_price], t1.[previous_running_sum_price],   
		t2.low_border, t2.up_border, t2.operation_serial, t2.payment_type  
	FROM MyCTE t1  
	LEFT JOIN _payments t2   
		ON t1.[invoice] = t2.[invoice]   
		AND t1.[running_sum_price] >= t2.low_border and t1.previous_running_sum_price <= t2.up_border  
)  
SELECT   
	t1.[invoice],t1.[instalment_id],t1.[current_instalment_price],t1.[running_sum_price], t1.[previous_running_sum_price],  
	t1.operation_serial, t1.low_border, t1.up_border,t1.payment_type,  
	The_Paymet = --t1.[running_sum_price] - t1.up_border  
		CASE  
			WHEN t1.[running_sum_price] >  t1.up_border THEN t1.up_border  
			WHEN t1.[running_sum_price] <= t1.up_border and t1.previous_running_sum_price <= t1.low_border THEN t1.[current_instalment_price] - t1.low_border  
			else t1.[current_instalment_price]  
		END  
FROM MyCTE2 t1  
GO  

Answer 2

Hello Ronen,

Seems very promising, thank you for your time; second case (the one with instalment amount in last column) is indeed what I was looking for. Will check all cases I have in payment table (more complex that two types of payments only) if also work fine, and let you know if your solution was great or brilliant :)

regards
Tomasz