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

