I have a markup table that have field like below
id markup from_date to_date
1 5 2013-01-20 2013-01-31
2 10 2013-02-01 2013-01-20
Now if I select date from 20 Jan - to 5 Feb then I should get Average markup
Ex- 20 jan to 24 Jan is 5 markup for 5 days
and 25 jan 4 fen is 10 markup for 10 days
so average markup i will get 8.3%
Though there seem to be few errors with your question.
Why is from_date once less than, and then greater than the to_date ? ( used ABS function to get number of days)
Assumed your average is Total of markup divided by total of days.
This is the query that would give you the desired response :
SELECT Sum(markup) / Sum(num_days) AS the_average
FROM (SELECT id,
markup,
from_date,
to_date,
Abs (Timestampdiff(day, from_date, to_date)) AS num_days
FROM test) AS t;
Something wrong with data and calculations in your example.
Anyway here is a SQLFiddle example
select sum(markup*days)/sum(days)
from
(
select Markup,
LEAST
( DATEDIFF(to_date,from_date)+1,
GREATEST(DATEDIFF('2013-02-05',from_date)+1,0),
GREATEST(DATEDIFF(to_date,'2013-01-20')+1,0)
) days
from t
where from_date between '2013-01-20' and '2013-02-05'
or
to_date between '2013-01-20' and '2013-02-05'
) t1