MySQL 5.5 - reduce table within SELECT

Question

I have a simple table and interesting task to do. I want to reduce the table from the initial

+----------+------------+--------+------------+--------+------------+
| NoClosed | Closed     | NoOpen | Open       | NoPlan | Plan       |
+----------+------------+--------+------------+--------+------------+
| 1        | 2018-10-23 | NULL   | NULL       | NULL   | NULL       |
| 2        | 2018-10-22 | NULL   | NULL       | NULL   | NULL       |
| 1        | NULL       | 1      | 2018-10-23 | NULL   | NULL       |
| NULL     | NULL       | 10     | 2018-10-25 | NULL   | NULL       |
| NULL     | NULL       | NULL   | NULL       | 1      | 2018-10-27 |
| NULL     | NULL       | NULL   | NULL       | 1      | 2018-10-28 |
| 10       | 2018-10-17 | NULL   | NULL       | NULL   | NULL       |
| 1        | NULL       | 1      | 2018-10-18 | NULL   | NULL       |
+----------+------------+--------+------------+--------+------------+

To this one:

Date    NoClosed    NoOpen  NoPlan
17/10/2018  10      
18/10/2018          
19/10/2018          
20/10/2018          
21/10/2018          
22/10/2018  2       
23/10/2018  3         1 
24/10/2018          
25/10/2018            10    
26/10/2018          
27/10/2018                   1
28/10/2018                   1
29/10/2018          
30/10/2018          
31/10/2018

It will be from -7 days to +7 days however I fail how to create the column with these dates inside select. I guess I need it to have a pivot point for all data and then Count it and group it.

My table code is here and any ideas will be appreciated.

CREATE TABLE Orders
(
NoClosed VARCHAR(20), 
Closed DATE, 

NoOpen VARCHAR(20),
Open DATE,

NoPlan VARCHAR(20), 
Plan DATE);




insert into Orders values (1,       "2018-10-23",   NULL,   NULL,   NULL, NULL);    
insert into Orders values (2,       "2018-10-22",   NULL,   NULL,   NULL, NULL);    

insert into Orders values (1,       NULL,       1,  "2018-10-23",   NULL,   NULL);
insert into Orders values (NULL,    NULL,       10, "2018-10-25",   NULL,   NULL);

insert into Orders values (NULL,    NULL,       NULL,   NULL,   1, "2018-10-27");   
insert into Orders values (NULL,    NULL,       NULL,   NULL,   1, "2018-10-28");
insert into Orders values (10,      "2018-10-17",   NULL,   NULL,   NULL, NULL);
insert into Orders values (1,       NULL,       1,  "2018-10-18",   NULL,   NULL);

Are you certain that your desired output is correct? Why are the other columns for `Date = 18/10/2018` empty? How come `NoClosed` is `3` when `Date = 23/10/2018`? — Nae, Oct 24 '18 at 15:37

score 0 · Answer 1 · answered Oct 24 '18 at 16:36

See What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)? for how to create a table that contains all the dates in a date range. Then left join this with a query that pivots your data:

SELECT d.date,
    MAX(CASE WHEN o.closed = date THEN o.NoClosed END) AS NoClosed,
    MAX(CASE WHEN o.open = date THEN o.NoOpen END) AS NoCLosed,
    MAX(CASE WHEN o.plan = date THEN o.NoPlan END) AS NoPlan
FROM DateTable AS d
LEFT JOIN Orders ON d.date IN (o.closed, o.open, o.plan)
GROUP BY d.date

MySQL 5.5 - reduce table within SELECT

1 Answers1