group by year, month with 2 datefields conditions

Question

I´m struggling around with this problem.

I have a Table with 3 columns:

id, startDate, endDate

1, 2014-01-01, 2014-02-02

2, 2014-04-03, NULL

3, 2014-02-02, 2014-05-03

now i want to count all ids with special conditions: for example for month may:

SELECT COUNT(*) FROM Table WHERE startDate <= "2014-05-31" and (endDate >= "2014-05-01" or endDate is NULL)

How can i achieve this by grouping in year and month?

startDate should be always <= lastDayofGivenMonth and endDate >= firstDayofGivenMonth or Null

The result should look like this:

year, month, count

2014, 01, 1

2014, 02, 2

2014, 03, 2

2014, 04, 2

2014, 05, 2

2014, 06, 1

...

Thank you for any help!

Steve

Dates in (my)sql adhere to a specific format. Until you get to grips with that, you're not going to get anywhere! — Strawberry, Nov 20 '14 at 09:53
i changed the date format. the date format is not the problem for me. — user2634127, Nov 20 '14 at 10:01
The date format absolutely is (or was) (at least part of) the problem!! — Strawberry, Nov 20 '14 at 10:02
Do you mean that if `startDate` is in January, `endDate` is in March, it should add 1 to the counts for January, February, and March? — Barmar, Nov 20 '14 at 10:04
@Barmar I *think* - and I may very well be wrong - that for each month the OP just wants to count the events that occur within or overlap that month — Strawberry, Nov 20 '14 at 10:07
for example for Month march: id 1 should be NOT count because endDate is less than firstDayOfMarch although startDate is less than lastDayofMarch. id2 should be count because startDate is less than lastDayofMarch and endDate is null. id3 should be count because startDate is less than lastDayofMarch and endDate is higher than firstDayofMarch. so the result is 2. hope that makes it a bit clearer — user2634127, Nov 20 '14 at 10:19

score 0 · Answer 1 · edited May 23 '17 at 10:26

First, create a table that contains all the months you care about:

CREATE TABLE months (
    year INTEGER,
    month INTEGER,
    start DATE,
    end DATE,
    PRIMARY KEY (year, month)
);
INSERT INTO months VALUES (2014, 1, '2014-01-01', '2014-01-31'), (2014, 2, '2014-02-01', '2014-02-28'), (2014, 3, '2014-03-01', '2014-03-31'), ...;

Then join this table with your event table:

SELECT m.year, m.month, COUNT(t.id) AS count
FROM months AS m
LEFT JOIN YourTable AS t
ON (startDate BETWEEN m.start AND m.end)
    OR (endDate IS NOT NULL 
        AND m.start BETWEEN startDate AND endDate)
GROUP BY year, month

I got the date range comparison from https://stackoverflow.com/a/14944239/1491895

DEMO

score 0 · Answer 2 · answered Nov 20 '14 at 10:21

My result set doesn't accord with yours, but are you looking for something like this...

I have a utility table of integers (0-9). I can use it to build a simple calendar of months...

 SELECT '2014-01-01' + INTERVAL i2.i*10+i1.i MONTH dt FROM ints i1,ints i2 HAVING dt < '2015-01-01';
 +------------+
 | dt         |
 +------------+
 | 2014-01-01 |
 | 2014-02-01 |
 | 2014-03-01 |
 | 2014-04-01 |
 | 2014-05-01 |
 | 2014-06-01 |
 | 2014-07-01 |
 | 2014-08-01 |
 | 2014-09-01 |
 | 2014-10-01 |
 | 2014-11-01 |
 | 2014-12-01 |
 +------------+

With this, I can do the following...

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,startDate DATE NOT NULL
 ,endDate DATE NULL
 );

 INSERT INTO my_table VALUES
 (1, '2014-01-01', '2014-02-02'),
 (2, '2014-04-03', NULL),
 (3, '2014-02-02', '2014-05-03');

 SELECT DATE_FORMAT(x.dt,'%Y-%m') yearmonth
      , COUNT(y.id) total
   FROM 
      ( SELECT '2014-01-01' + INTERVAL i2.i*10+i1.i MONTH dt FROM ints i1,ints i2 HAVING dt < '2015-01-01' ) x
   LEFT
   JOIN my_table y
     ON DATE_FORMAT(x.dt,'%Y-%m') >= DATE_FORMAT(startdate,'%Y-%m') 
    AND (DATE_FORMAT(x.dt,'%Y-%m') <= DATE_FORMAT(enddate,'%Y-%m') OR y.enddate IS NULL)
  GROUP
     BY yearmonth;
 +-----------+-------+
 | yearmonth | total |
 +-----------+-------+
 | 2014-01   |     1 |
 | 2014-02   |     2 |
 | 2014-03   |     1 |
 | 2014-04   |     2 |
 | 2014-05   |     2 |
 | 2014-06   |     1 |
 | 2014-07   |     1 |
 | 2014-08   |     1 |
 | 2014-09   |     1 |
 | 2014-10   |     1 |
 | 2014-11   |     1 |
 | 2014-12   |     1 |
 +-----------+-------+

score 0 · Accepted Answer · answered Nov 20 '14 at 11:22

You will need a date range (year, month) to test against. You can create this range in different ways such as:

SELECT Y, M, DATE_ADD(MAKEDATE(Y, 1), INTERVAL M -1 MONTH) AS FirstDay, LAST_DAY(DATE_ADD(MAKEDATE(Y, 1), INTERVAL M -1 MONTH)) AS LastDay
FROM
  (select 2013 as Y union all select 2014 union all select 2015) years
cross join 
  (select 1 as M union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) months

Then it will be a simple join and grouping:

SELECT Y, M, count(d.id)
FROM (
  SELECT Y, M, DATE_ADD(MAKEDATE(Y, 1), INTERVAL M -1 MONTH) AS FirstDay, LAST_DAY(DATE_ADD(MAKEDATE(Y, 1), INTERVAL M -1 MONTH)) AS LastDay
  FROM
   (select 2013 as Y union all select 2014 union all select 2015) years
    cross join 
   (select 1 as M union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) months
) YM
LEFT JOIN dates d ON startDate <= YM.LastDay AND (endDate IS NULL OR endDate >= YM.FirstDay) 
GROUP BY Y, M

group by year, month with 2 datefields conditions

3 Answers3