Apply cumulative discount to a table

Question

To the table I need to calculate DISCOUNT_AMOUNT and AMOUNT_REST as follows in the formula view. For every NAME there is a unique amount repeated in every row, but the discount isnt AMOUNT*DISCOUNT% in all rows, only the first discount order by ascending DATE. The next discounts for the same NAME are applied to the AMOUNT_REST.

A way to solve this in SQL Server language?

select [NAME], [AMOUNT], [DISCOUNT%], [DATE]--, DISCOUNT_AMOUNT, AMOUNT_REST
FROM [Table]
order by [NAME], [DATE] asc

This is what a I want:

[NAME]	[AMOUNT]	[DATE] (dd-MM-yyyy)	[DISCOUNT%]	[DISCOUNT_AMOUNT]	[AMOUNT_REST]
Peter	$100	01-01-2023	4%	$4,0	$96,0
Peter	$100	02-01-2023	20%	$19,2	$76,8
Peter	$100	03-01-2023	5%	$3,8	$73,0
John	$500	01-01-2023	40%	$200,0	$300,0
John	$500	02-01-2023	3%	$9,0	$291,0
Sara	$200	01-01-2023	9%	$18,0	$182,0
Sara	$200	02-01-2023	10%	$18,2	$163,8

This is the Excel-manual way to do it

[NAME]	[AMOUNT]	[DATE] (dd-MM-yyyy)	[DISCOUNT%]	[DISCOUNT_AMOUNT]	[AMOUNT_REST]
Peter	100	44927	0,04	B2*D2	B2*(1-D2)
Peter	100	44928	0,2	F2*D3	F2*(1-D3)
Peter	100	44929	0,05	F3*D4	F3*(1-D4)
John	500	44927	0,4	B5*D5	B5*(1-D5)
John	500	44928	0,03	F5*D6	F5*(1-D6)
Sara	200	44927	0,09	B7*D7	B7*(1-D7)
Sara	200	44928	0,1	F7*D8	F7*(1-D8)

There are "cumulative SQL" questions which show a general approach - for example: [How to get cumulative sum](https://stackoverflow.com/q/2120544/12567365) shows some different approaches (and you would have to replace their `sum` with the discount calculation you need). There are also more examples [in these questions](https://www.google.com/search?q=cumulative+sql+site:stackoverflow.com). — andrewJames, May 19 '23 at 21:51
@andrewJames: that's a cumulative *discount*, which seems like a more complicated beast than just a window `sum`. There are probably duplicates available, but likely there are rarest and harder to adapt from one use case to anther. — GMB, May 19 '23 at 22:16

score 1 · Accepted Answer · answered May 19 '23 at 22:10

Although there might be a mathematical approach to compute the cumulative discount using window functions, this seems more easily approached with a recursive query:

with 
    data as (
        select t.*, row_number() over(partition by name order by date) rn
        from mytable t
    ),
    rcte as (
        select name, amount, date, discount_pct, rn,
            amount * discount_pct       as discount_amount,
            amount * (1 - discount_pct) as rest_amount
        from data
        where rn = 1
        union all
        select r.name, r.amount, d.date, d.discount_pct, d.rn
            r.rest_amount * d.discount_pct,
            r.rest_amount * (1 - d.discount_pct)              
        from rcte r
        inner join data d on d.name = r.name and d.rn = r.rn + 1
    )
select * from rcte

The first CTE, data, enumerates the rows of each name. Then rcte starts from the first row of each partition and iterates, basically applying the same computation logic as the Excel formulas in your question.

You got it right, code works. But sadly this code doesn't run for big data sets (server performance i guess). Maybe should try some Python... — Alan, Jun 18 '23 at 07:21

p3consulting · Answer 2 · 2023-05-22T16:23:21.887

Math: log (a+b) = log(a) * log(b), so we can calculate exp(sum(log(col))

https://dbfiddle.uk/IZMXyxUq

with data(name, amount, dat, discount) as (
    select 'Peter', 100, convert(DATE, '01-01-2023',105), 4  union all
    select 'Peter', 100, convert(DATE,'02-01-2023',105), 20  union all
    select 'Peter', 100, convert(DATE,'03-01-2023',105),5  union all
    select 'John', 500, convert(DATE,'01-01-2023',105),40  union all
    select 'John', 500, convert(DATE,'02-01-2023',105),3  union all
    select 'Sara', 200, convert(DATE,'01-01-2023',105),9  union all
    select 'Sara', 200, convert(DATE,'02-01-2023',105),10  -- union all
),
rdata as (
    select * from (
        select d.name, d.amount, d.dat, d.discount / 100.0 as discount, 
            log(1.0 - (d.discount / 100.0)) as ln_rest_factor, 
        row_number() over(partition by name order by dat) as rn
        from data d
    ) d
)
select name, amount, discount, coalesce(lag(discount_rest) over(partition by name order by rn),amount) - discount_rest as discount_amount, discount_rest 
from (
    select name, amount, discount, round(amount * exp(sum_factor),2) as discount_rest, rn
    from (
        select name, amount, dat, discount, rn, ln_rest_factor,
            sum(ln_rest_factor) over(partition by name order by rn) as sum_factor
        from rdata
    ) d
) d
order by name, rn
;

Apply cumulative discount to a table

2 Answers2