sqlsql-serverlogicwhere-clausesql-server-2019

Work out in SQL the total money made from increments


I want to work out money made by our automated system

say

Product Margin Previous Margin Type Purchases
Oranges 1 0 Manual 3
Oranges 2 1 Auto 4
Oranges 3 2 Auto 6
Oranges 4 3 Auto 3
Oranges 1 4 Manual 7
Oranges 2 1 Auto 4

I want to work out how much money we have made for each margin amount, so I would do Margin x Purchases. However I want to work out the total money has been made from these automated increases by the system. I could do where type = Auto, but I'm interested in the money made from the increments.

The first auto increase is £1 from the last manual increase, so instead of saying the automated money made is £2 * 4 purchases = £8, we would say £1 of the margin came from automated, so £1 * 4 purchases = £4 so for the first auto increase we made £4 from the automated system.

That's one thing to calculate.

The next auto increase is an increase from a previous auto increase but it is in fact £2 increase from the initial manual increase. We would say from that £3, £1 was already set and £2 has been set automatically (including both automated since we want to include all automated).

and same applied to next row.

but when we get to the 5th row there is a manual adjustment and then following that a automated increase. So now we need to reset the increment amount from the last manual change. There is a £1 increase from the manual change.

How can I get back the total automated money made based on these increments so we can work out the value of these automations.

Looking for a SQL based solution for SQL Server 2019+. Open to creating multiple tables, temp tables, etc.

Expected result

Product Margin Previous Margin Type Purchases AuIncrement AuRevenue
Oranges 1 0 Manual 3 N/A N/A
Oranges 2 1 Auto 4 1 4
Oranges 3 2 Auto 6 2 12
Oranges 4 3 Auto 3 3 9
Oranges 1 4 Manual 7 N/A N/A
Oranges 2 1 Auto 4 1 4

AuIncrement is the increment for every automated margin (current au margin - the last manual margin).

AuRevenue is margin x AUincrement

So the key is working out this AUincrement


Solution

  • https://dbfiddle.uk/PTCG3WY4

    This assumes you have some column available that tells you the order. Tables aren't intrinsically ordered in a useful fashion, you have to tell it what "first" and "last" mean to you.

    In this case it's a load date, could easily be a monotonically increasing id too.

    Also assumes you want this by product . I.e. If you had "apples" in your data those calculations wouldn't be influenced by the data in "oranges". If that's not true just remove all the conditions on product.

    Curious to see the other answers you get. There's a sql server function LAG() with an optional IGNORE NULLs clause that would really clean this up. But at least in the fiddle it's not supported by SQL Server 2019.

    Generally idea is you use a correlated subquery to get the last manual transaction date for each auto transaction. Then use that to self join back on that last transaction date to get the associated purchase value.

    I've tested for your sample data, but it's always good to make sure that sample data covers all the cases you are apt to see.

    create table some_test_data (
      product varchar(255),
      margin integer,
      previous_margin integer,
      type varchar(255),
      purchases varchar(255),
      load_date date
    )
    
    INSERT INTO some_test_data VALUES
    ('Oranges', 1, 0, 'Manual', 3, '2023-01-01'),
    ('Oranges', 2, 1, 'Auto', 4, '2023-01-02'),
    ('Oranges', 3, 2, 'Auto', 6, '2023-01-03'),
    ('Oranges', 4, 3, 'Auto', 3, '2023-01-04'),
    ('Oranges', 1, 4, 'Manual', 7, '2023-01-05'),
    ('Oranges', 2, 1, 'Auto', 4, '2023-01-06');
    
    with append_last_manual_load_date as (
      select 
           t1.*,
           apl.last_manual_load_date
      from some_test_data t1
     outer
     apply ( select max(load_date) as last_manual_load_date
               from some_test_data t2
              where t1.product = t2.product
                and t1.load_date > t2.load_date
                and t2.type = 'Manual'
                and t1.type = 'Auto'
           ) apl
    )
    select ltd.product,
           ltd.margin,
           ltd.previous_margin,
           ltd.type,
           ltd.purchases,
           ltd.load_date,
           ltd.margin - orig_assc_with_last_manual_date.margin as AuIncrement,
           ltd.purchases*(ltd.margin - orig_assc_with_last_manual_date.margin) as AuRevenue
      from append_last_manual_load_date ltd
      left
      join some_test_data orig_assc_with_last_manual_date
        on orig_assc_with_last_manual_date.product = ltd.product
       and orig_assc_with_last_manual_date.load_date = ltd.last_manual_load_date
     order
        by ltd.load_date
    
    product margin previous_margin type purchases load_date AuIncrement AuRevenue
    Oranges 1 0 Manual 3 2023-01-01 null null
    Oranges 2 1 Auto 4 2023-01-02 1 4
    Oranges 3 2 Auto 6 2023-01-03 2 12
    Oranges 4 3 Auto 3 2023-01-04 3 9
    Oranges 1 4 Manual 7 2023-01-05 null null
    Oranges 2 1 Auto 4 2023-01-06 1 4