How to create a complex query that summarizes conditions with 2 tables?

Question

I am trying to get the total monthly amount of net_insurance during the term of the policy.

Here are my policies in the table:

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 are my table insurance

 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 be summarized in accordance with this condition (during the life cycle of the policy).

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

According to the condition, I must 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 making the total amount 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 my request does not work, I have other 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 can anyone help me with this?

I will be very grateful for the 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) ; 

Answer 2

Combining your post, editing, comments, and SQL script and making a few guesses, it looks like you need something like this:

 SELECT LEFT(DATE_FORMAT(I.initial_date, '%M'), 3) AS Month, SUM(I.net_insurance) AS Insurance FROM insurances AS I INNER JOIN policies AS P ON I.policy_id = P.id WHERE YEAR(P.date_ini) = 2013 AND YEAR(P.date_expired) = 2013 GROUP BY LEFT(DATE_FORMAT(I.initial_date, '%M'), 3) 

Regarding the rotation of the resulting data, this, unfortunately, is rather cumbersome in MySQL. It has been covered in other sections of SO ( MySQL PivotTable ).