php - Calculate Rate based on distance range -

i have been working project have following table structure

 start | end | rate ------------------    1  |  50 | 10   51  | 100 | 20  101  | 150 | 40  151  | 200 | 80  201  |   0 |100

here last record means 200 infinite value has rate 100

here have calculate total rate based on start , end values given users

i have tried following query in mysql input start - 30, end - 170

select sum((end+1 - start) * amount)  table  start > 30 , end < 170

which gives 2nd , 3rd record values sum, have query first , last records separately.

how achieve in single query?

let's assume have following 2 parameters declared:

set @start = 30, @end = 170;

first rows fall within range (overlapping), (sql fiddle):

select start, end table1 start <= @end or end >= @start

then massage start , end of ranges first , last rows. capping first part of range @ @start , second part of range @ @end (sql fiddle):

select case when @start > start @start else start end start,      case when @end < end @end else end end end table1 start <= @end or end >= @start

then can subtract these 2 case expressions distance. , don't forget add 1 if want inclusive distance (sql fiddle):

select case when @end < end @end else end end - case when @start > start @start else start end + 1 dist table1 start <= @end or end >= @start

multiply rate. time don't forget parentheses, since multiplication takes precedence (sql fiddle):

select rate * (case when @end < end @end else end end - case when @start > start @start else start end + 1) table1 start <= @end or end >= @start

and finally, slap sum around whole expression add (sql fiddle):

select sum(rate * (case when @end < end @end else end end - case when @start > start @start else start end)) table1 start <= @end or end >= @start

Bradly