How can I create a complex query that sum conditions with 2 tables?

Question

I'm trying to get the total sum by months of net_insurance during a policy's lifetime.

Here is my table policies:

ID       date_ini      date_expired  num_policy
1,     '2013-01-01',   '2014-03-08',    1234       
2,     '2012-02-11',   '2013-02-01',    5678  
3,     '2013-03-01',   '2013-08-03',    9123 
4,     '2013-04-01',   '2013-08-01',    4567 
5,     '2013-05-01',   '2013-09-01',    8912 

Here is my table insurances

ID      initial_date     final_date  policy_id  net_insurance
1,     '2013-01-16',   '2014-01-01',      1,     1000       
2,     '2013-01-14',   '2014-03-06',      1,     1400
3,     '2012-03-17',   '2013-04-24',      2,     2000  
4,     '2012-02-12',   '2013-02-01',      2,     2500 
5,     '2013-03-09',   '2013-08-20',      3,     3000 
6,     '2013-03-11',   '2013-08-02',      3,     4000 

It will sum according to this condition(during a policy's lifetime).

WHERE insurances.initial_date >= policies.date_ini
  AND   insurances.final_date <= policies.date_expired                     

According with the condition I should have this:

   ID   NUM_POLICY  SUM_INSURANCE
    1   1234              2400
    2   5678              2500
    3   9123              4000
    4   4567               0
    5   8912               0 

This will do:

 |jan|   |feb|  |mar|  |apr| |may|  |jun|  |jul| |ago| |sep| |oct| |nov| |dec|
  2400    2400  2400    2400  2400    2400  2400  2400  2400   2400 2400   2400
  2500    2500  _______________________________________________________________
  ______________4000    4000  4000   4000  4000  4000  ________________________
  _______________________0    0       0      0    0    ________________________
  ____________________________0       0      0    0     0______________________

Here is the final result doing the total sum of each month:

 |jan|   |feb|  |mar| |apr|  |may  |jun| |jul| |ago| |sep| |oct| |nov| |dec|
  4900   4900   6400   6400  6400  6400  6400  6400  2400   2400  2400 2400

But is not working my query i'm having another values:

 JAN   FEB    MAR     APR     MAY     JUN   JUL   AUG      SEP     OCT     NOV  DEC
 8900  8900  8900   8900    8900    8900    8900    8900    8900    8900    8900    8900

Here is what I tried http://sqlfiddle.com/#!2/e75ea/1

Please somebody can help me with this?

I will really appreciate help.

Answer 1

You can try with this query:

SET @year := 2013;

SELECT
SUM(if (CONCAT(@year, '-01') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Jan,
SUM(if (CONCAT(@year, '-02') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Feb,
SUM(if (CONCAT(@year, '-03') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Mar,
SUM(if (CONCAT(@year, '-04') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Apr,
SUM(if (CONCAT(@year, '-05') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) May,
SUM(if (CONCAT(@year, '-06') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Jun,
SUM(if (CONCAT(@year, '-07') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Jul,
SUM(if (CONCAT(@year, '-08') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Aug,
SUM(if (CONCAT(@year, '-09') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Sep,
SUM(if (CONCAT(@year, '-10') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Oct,
SUM(if (CONCAT(@year, '-11') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Nov,
SUM(if (CONCAT(@year, '-12') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) `Dec`

FROM insurances  i
INNER JOIN policies p ON p.id = i.policy_id 
WHERE (i.initial_date >= p.date_ini
   AND i.final_date   <= p.date_expired) 
  ;