SQLTeam.com | Weblogs | Forums

Calculating fees per order based on monthly sales


#1

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?


#2

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
;

#3

Woah, I didn't know this was here. Thanks for posting the answer!