i have trouble understanding how can solve problem t-sql query. have price column , volume column. in table have discounts @ different levels of volume. discount table have values
(startlevel, discountfactor) (0, 1); (25, 0.95); (50, 0.90); (100, 0.75) what want calculate total price. if volume 35, want multiply
price x ((35-25) x 0.95 + (25-0) x 1) if volume 200, should be
price x ((200-100) x 0.75 + (100-50) x .9+(50-25) x .95+(25) x 1) can me query solves this?
this can help:
declare @products table ( id int , price money , volume int ) declare @discounts table ( id int , level int , factor money ) insert @products values ( 1, 10, 35 ), ( 2, 15, 200 ) insert @discounts values ( 1, 0, 1 ), ( 2, 25, 0.95 ), ( 3, 50, 0.90 ), ( 4, 100, 0.75 ) select p.id, p.price * sum(ca.m) @products p cross apply ( select * , factor * ( -level + lead(level) on ( partition p.id order level, d ) ) m ( select 1 d , level , factor @discounts level < p.volume union select 2 d , p.volume , 0 ) t ) ca group p.id without grouping returns:
id price volume d level factor m 1 10.00 35 1 0 1.00 25.00 1 10.00 35 1 25 0.95 9.50 1 10.00 35 2 35 0.00 null 2 15.00 200 1 0 1.00 25.00 2 15.00 200 1 25 0.95 23.75 2 15.00 200 1 50 0.90 45.00 2 15.00 200 1 100 0.75 75.00 2 15.00 200 2 200 0.00 null then group by product , sum of m results in:
id total 1 345.00 2 2531.25 
Comments
Post a Comment