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.

You want to get the total of `net_insurance` of all `insurance` records which were wholly in effect during a policy's lifetime? Sounds reasonable. Do you have a `Dates` table? Your manual pivoting is making my eyes cross. — , Dec 16 '13 at 23:20
The first two links work fine, thanks. Do you have a table which stores date-related fields? Here are a couple articles: http://www.techrepublic.com/blog/the-enterprise-cloud/simplify-sql-server-2005-queries-with-a-dates-table/326/ http://sqlserverplanet.com/dba/create-date-table Such a table should save you from the cumbersome date logic you have drafted. — , Dec 17 '13 at 15:34
There is just one record in your sample data where where an insurance event occurs wholly within a policy (insurance event #1 is within policy period #1). This record is excluded by the filter (in Fiddle) that the policy must start and end in 2013. Are you sure about these filters? Perhaps it would make sense to filter on events' start dates rather than both start and end dates? — , Dec 17 '13 at 15:38
@JonofAllTrades @Carlitos Morales I agree that there is only one row that meets the condition `WHERE insurances.initial_date >= policies.date_ini AND insurances.final_date <= policies.date_expired` if you join on the condition `insurances.policy_id = policies.id` hence the question does not match the sqlfiddle — abasterfield, Dec 17 '13 at 16:49
So how do you want to join `policies` and `insurances`? By ID or by date? — , Dec 17 '13 at 20:41

score 3 · Accepted Answer · answered Dec 18 '13 at 08:52

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) 
  ;

Please check this query http://sqlfiddle.com/#!2/a2638/6 . I'm trying to get the sum from 2 queries ,the first query has the sum with type_money= 1 and the second with type_money= 2. I'm trying to get a final sum — Carlos Morales, Dec 18 '13 at 17:02
Please, look at http://sqlfiddle.com/#!2/a2638/19/0 and tell me if that solve your problem. — gieffe, Dec 18 '13 at 17:26

score 2 · Answer 2 · edited May 23 '17 at 10:33

2

Cobbling together your post, edits, comments, and SQL fiddle, and making a number of guesses, it sounds 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)

As for pivoting the resulting data, that is unfortunately rather cumbersome in MySQL. It's covered elsewhere on SO (MySQL pivot table).

edited May 23 '17 at 10:33

Community

1
1

answered Dec 17 '13 at 21:51

please can you read all my post again? i added details fixed data and also did a query but is not doing the sum correctly – Carlos Morales Dec 17 '13 at 22:44
Here is what i want http://sqlfiddle.com/#!2/e75ea/1 but is not doing a correct sum – Carlos Morales Dec 17 '13 at 22:48
@CarlitosMorales: I've read your post several times, but it is not at all specific. "Is not doing the sum correctly" is extremely vague. I suggest you start over with a clear explanation of what you want to achieve, include your draft query, and an explanation of how your draft does not meet your expected output. If nothing else, you need to make it clear whether you're asking about how to aggregate the data or how to pivot it. – Dec 18 '13 at 15:15

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

2 Answers2