SQL Server Apply cumulative discount to a table

t5fffqht 于 2023-05-21 发布在其他

关注(0)|答案(2)|浏览(136)

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)

sql-server

来源：https://stackoverflow.com/questions/76292532/apply-cumulative-discount-to-a-table

2条答案

按热度按时间

ar7v8xwq1#

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.

赞(0）回复(0）举报 2023-05-21

hyrbngr72#

Math: log (a+b) = log(a) * log(n), 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
;

赞(0）回复(0）举报 2023-05-21

我来回答

SQL Server Apply cumulative discount to a table

2条答案

相关问题

热门标签

最新问答