I have a table that looks like the one below. Orders appear with dates, IDs & totals. I need to calculate the FeeValue for each order.
Sales:
Separately I have another table with tiered pricing.
Tiered Fees:
How would I query the database to give me 10% for the first two orders & 7% for the second two?
Try this:
with cte(salesdate,orderid,totalsale,totalsale_acc)
as (select salesdate
,orderid
,totalsale
,sum(totalsale) over(order by salesdate
rows between unbounded preceding and current row
)
as totalsale_acc
from sales
)
select a.salesdate
,a.orderid
,a.totalsale
,b.feepercentage as feevalue
from cte as a
left outer join [tiered fees] as b
on isnull(b.[min],a.totalsale_acc)<=a.totalsale_acc
and isnull(b.[max],a.totalsale_acc)>=a.totalsale_acc
;
Woah, I didn't know this was here. Thanks for posting the answer!