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
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 |