MYSQL crosstab with daterange analysis: days as rows, sum as columns

Question

I have a table like this, showing the dates where patients were administered:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL DEFAULT '0',
  `datefrom` datetime DEFAULT NULL,
  `timefrom` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`));

My data looks like this:

SELECT id, datefrom, dateto FROM table1;

1022, 2016-03-16 00:00:00, 2016-03-18 00:00:00
1023, 2016-03-18 00:00:00, 2016-03-24 00:00:00
1024, 2016-03-14 00:00:00, 0000-00-00 00:00:00
1025, 2016-03-29 00:00:00, 2016-03-31 00:00:00
1026, 2016-03-29 00:00:00, 2016-03-30 00:00:00
1027, 2016-03-30 00:00:00, 2016-03-31 00:00:00
1028, 2016-03-31 00:00:00, 0000-00-00 00:00:00

I'm interested in showing a crosstab with

days as rows (starting at the min of datefrom and ending at the max of dateto)
sum of patients, who were administered on this particular day

Additionally if no dateto is given, the patient is administered until today.

A expected result of the data above should look like (only first rows):

2016-03-14, 2
2016-03-15, 1
2016-03-16, 2
2016-03-17, 2
2016-03-18, 3
2016-03-19, 2
2016-03-20, 2

Add the expected result (with the already given input.) – jarlh Apr 01 '16 at 08:52 — jarlh, Apr 01 '16 at 08:52

score 1 · Accepted Answer · answered Apr 01 '16 at 09:27

You can introduce a collateral table with all dates (could be created once and filled with all day dates). Then you can use

SELECT adt.date_column,
       COUNT(*)
FROM the_all_dates_table adt
     JOIN table1 t1 ON adt.date_column BETWEEN t1.datefrom AND t1.dateto
GROUP BY adt.date_column

MYSQL crosstab with daterange analysis: days as rows, sum as columns

1 Answers1