question

TomaszSerwaski-2701 avatar image
0 Votes"
TomaszSerwaski-2701 asked EchoLiu-msft commented

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

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





sql-server-transact-sql
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It certainly helps for a question like this if you post CREATE TABLE statements for your tables and the sample data as INSERT statements. This makes it easy to copy and paste into a query window to develop a tested solution. To verify the solution, we also need to have the expected result given the test data.

It's also a good idea to share which version of SQL Server you are working on.

0 Votes 0 ·

right; just addedd

regards
Tomasz

0 Votes 0 ·
pituach avatar image pituach TomaszSerwaski-2701 ·

Hi,

(1) A bit more data to cover more cases like for example for another [invoice] + the related expected result in a table format

(2) why do you need the column [total_instalment_number] ? Is this value not the same which we can count from the table? So why do you need to store the value so many times in each row

(3) And why do you need the column [invoice_with_instalment_id] ?!? This is simply a combination of two other columns. If you really need this column then at least make it a computed column, but it seems like something which is not needed and better remove it in first glance

(4) Same with the columns [running_sum_price],[invoice_total]

Not clear why you need this complex table instead simple one (maybe this was for the sake of the question to explain?)

0 Votes 0 ·

Hi @TomaszSerwaski-2701

Welcome to the microsoft TSQL Q&A forum!
Could you provide the expected output of your example?

Regards
Echo

0 Votes 0 ·

Do you have any update?

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

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

119542-image.png

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

119574-image.png



image.png (41.7 KiB)
image.png (38.4 KiB)
image.png (41.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

By the way, we can solve this question using GEOMETRY data type as well

Using GEOMETRY data type we can solve all kind of overlapping ranges issues. It might provide better solution if there are no indexes as the current DDL+DML and it will be simpler to cover all cases.

An example of a similar dealing with overlapping ranges you can see in this post:

https://ariely.info/Blog/tabid/83/EntryId/228/Grouping-continuous-Ranges-together-Part-2-Using-geometry-Data-type.aspx

1 Vote 1 ·
TomaszSerwaski-2701 avatar image
0 Votes"
TomaszSerwaski-2701 answered pituach commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You are most welcome

Unfortunately, there is no way in this forum system to follow our threads. So when you are back, then you can ping me on Facebook and I will come to continue the discussion according to the time I find.

----------

💬Ronen Ariely


Personal Site | Blog | Facebook | Linkedin

1 Vote 1 ·