SQLTeam.com | Weblogs | Forums

Calculating fees per order based on monthly sales


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.


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
            ,sum(totalsale) over(order by salesdate
                                 rows between unbounded preceding and current row 
             as totalsale_acc
        from sales
select a.salesdate
      ,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!