How can I create a query using a range of dates and count per month?

Question

I'm trying to create a range to my policies

Here is my table

|policies|
  |id|   |date_ini|   |date_end|
   1     2013-01-01   2014-01-01      
   2     2012-02-01   2013-02-01     
   3     2013-03-01   2013-03-03     
   4     2013-04-01   2013-08-01

I'm trying to create a range like this

 date_ini <=    range_of_each_policy    <= date_end

Here is the logic using year 2013

|jan| |feb| |mar| |apr| |may| |jun| |jul| |ago| |sep| |oct| |nov| |dic|
 id1   id1   id1   id1   id1   id1   id1   id1   id1   id1   id1   id1
 id2   id2   __________________________________________________________
 __________  id3 ______________________________________________________
 _______________   id4   id4   id4   id4   id4  _______________________

Also it will show per month

 jan  feb   mar  apr.........
  3    3     2    2....

How can I do that?

fthiella · Accepted Answer · 2013-11-15T20:55:16.137

Not the cleanest query ever... but is this what you are looking for?

SET @year := 2013;

SELECT
  CASE WHEN CONCAT(@year, '-01-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END Jan,
  CASE WHEN CONCAT(@year, '-02-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END Feb,
  CASE WHEN CONCAT(@year, '-03-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END Mar,
  CASE WHEN CONCAT(@year, '-04-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END Apr,
  CASE WHEN CONCAT(@year, '-05-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END May,
  CASE WHEN CONCAT(@year, '-06-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END Jun,
  CASE WHEN CONCAT(@year, '-07-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END Jul,
  CASE WHEN CONCAT(@year, '-08-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END Aug,
  CASE WHEN CONCAT(@year, '-09-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END Sep,
  CASE WHEN CONCAT(@year, '-10-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END Oct,
  CASE WHEN CONCAT(@year, '-11-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END Nov,
  CASE WHEN CONCAT(@year, '-12-01') BETWEEN date_ini AND date_end THEN CONCAT('id', id) END `Dec`
FROM
  policies
WHERE
  date_end >= CONCAT(@year, '-01-01')
  AND 
  date_ini <= CONCAT(@year, '-12-01')

But I think that it's usually better to represent the results using some code, eg. with PHP, rather than using a MySQL query.

Fiddle is here.

Edit

And this query is for the sum:

SET @year := 2013;

SELECT
  SUM(CONCAT(@year, '-01-01') BETWEEN date_ini AND date_end) Jan,
  SUM(CONCAT(@year, '-02-01') BETWEEN date_ini AND date_end) Feb,
  SUM(CONCAT(@year, '-03-01') BETWEEN date_ini AND date_end) Mar,
  SUM(CONCAT(@year, '-04-01') BETWEEN date_ini AND date_end) Apr,
  SUM(CONCAT(@year, '-05-01') BETWEEN date_ini AND date_end) May,
  SUM(CONCAT(@year, '-06-01') BETWEEN date_ini AND date_end) Jun,
  SUM(CONCAT(@year, '-07-01') BETWEEN date_ini AND date_end) Jul,
  SUM(CONCAT(@year, '-08-01') BETWEEN date_ini AND date_end) Aug,
  SUM(CONCAT(@year, '-09-01') BETWEEN date_ini AND date_end) Sep,
  SUM(CONCAT(@year, '-10-01') BETWEEN date_ini AND date_end) Oct,
  SUM(CONCAT(@year, '-11-01') BETWEEN date_ini AND date_end) Nov,
  SUM(CONCAT(@year, '-12-01') BETWEEN date_ini AND date_end) `Dec`
FROM
  policies
WHERE
  date_end >= CONCAT(@year, '-01-01')
  AND 
  date_ini <= CONCAT(@year, '-12-01')

Fiddle here.

fthiella how can i count each month?. for example count all in jan, feb,mar... your code is right but i need also that. Can you help me with this please? — Carlos Morales, Nov 15 '13 at 20:46
@CarlitosMorales please see my edited answer. But are you sure that jan and feb should give 3? I get 2 and if I understand your logic, it should be correct... — fthiella, Nov 15 '13 at 20:56
Ups sorry, my bad , you were right i'll check your code in a few minutes :) — Carlos Morales, Nov 15 '13 at 21:00
OMG.......Fantastic job ..."tu sei grande" =) ....You did it , i thought that nobody could help with this. Thanks and won point =) — Carlos Morales, Nov 15 '13 at 21:49
I have a last question can you explain me this? date_end >= CONCAT(@year, '-01-01') AND date_ini <= CONCAT(@year, '-12-01') — Carlos Morales, Nov 15 '13 at 22:00
@CarlitosMorales muchas gracias :) i see from your example that the day of the month is always 1, is that always the case? if not, we should fix the query! `date_end >= CONCAT(@year, '-01-01') AND date_ini <= CONCAT(@year, '-12-01')` is needed if you want to include only the rows that have a month in the selected @year, so date_end needs to be <= '2013-01-01' and date_ini needs to be <= '2013-12-01' ... but if date_ini could be for example 2013-12-15 we need to fix something.... — fthiella, Nov 16 '13 at 13:11

How can I create a query using a range of dates and count per month?

1 Answers1