mysql - Total Price between date range (like hotel reservation seasons) -

 id       carid     date1         date2      price  ---      ----     ---------    ---------    -----  1        1       1000-01-01    9999-12-31   100   2        2       1000-01-01    2014-06-06   100   3        2       2014-06-07    2014-06-07   150  4        2       2014-06-08    9999-12-31   100   5        3       1000-01-01    2014-06-01   150  6        3       2014-06-02    2014-07-01   200  7        3       2014-07-02    2014-07-25   300  8        3       2014-07-26    9999-12-31   100  

this price table; want calculate total price dates range.

i want display cars total price;

for example filter dates:

date1: 2014-06-01
date2: 2014-06-22

results

carid    total ------ ------ 1      2100 2      2150 3      4150 

and try , 1 carid.
calculate price between given dates
this;
calculate price between given dates in multiple ranges

assuming have variables @date1 , @date2, can conditional aggregation:

select carid,        sum(1 + greatest(least(date2, @date2) - greatest(date1, @date1), 0) * price) table t group carid; 

this replaces boundaries of each period in table intersection outer boundaries. sums duration times price. 1 + seems handle case date2 appears valid date price, rather first date when not active.