MySQL 5.5 - count open items per day

Question

I have the below table that is just a snapshot and all I want to do is to calculate the number of open items per date.

I used to do it in excel with simple formula =COUNTIFS($A$2:$A$30000,"<="&E2,$B$2:$B$30000,">="&E2) where column A was the Open_Date dates and column B the Close_Date dates. I want to use SQL to get the same results.

This is my excel snapshot. Formula above.

In mysql I have replicated it with T1 table:

CREATE TABLE T1
(

ID int (10),
Open_Date date,
Close_Date date);


insert into T1 values (1, '2018-12-17', '2018-12-18');
insert into T1 values (2, '2018-12-18', '2018-12-18');
insert into T1 values (3, '2018-12-18', '2018-12-18');
insert into T1 values (4, '2018-12-19', '2018-12-20');
insert into T1 values (5, '2018-12-19', '2018-12-21');
insert into T1 values (6, '2018-12-20', '2018-12-22');
insert into T1 values (7, '2018-12-20', '2018-12-22');
insert into T1 values (8, '2018-12-21', '2018-12-25');
insert into T1 values (9, '2018-12-22', '2018-12-26');
insert into T1 values (10, '2018-12-23', '2018-12-27');

First step was to create the table with dates in case there any gap in Date_open. So my code at the moment is

SELECT
    d.dt, Temp_T1.*
FROM
(
    SELECT '2018-12-17' AS dt UNION ALL
    SELECT '2018-12-18' UNION ALL
    SELECT '2018-12-19' UNION ALL
    SELECT '2018-12-20' UNION ALL
    SELECT '2018-12-21' UNION ALL
    SELECT '2018-12-22' UNION ALL
    SELECT '2018-12-23' UNION ALL
    SELECT '2018-12-24'
) d

LEFT JOIN 
(SELECT * FROM T1) AS Temp_T1
ON Temp_T1.Open_Date = d.dt

I am lost how to calculate the same values as I do in excel?

I have just tried below code with nil luck: `SELECT Open_Date , ID , COUNT(Open_Date) ,COUNT(IF(T1.Open_Date >= '2018-12-17' AND T1.Close_Date <= '2018-12-24' ,1, NULL)) AS A ,COUNT(IF(T1.Open_Date >= '2018-12-18' AND T1.Close_Date <= '2018-12-24' ,1, NULL)) AS B ,COUNT(IF(T1.Open_Date >= '2018-12-19' AND T1.Close_Date <= '2018-12-25' ,1, NULL)) AS B FROM T1 GROUP BY Open_Date;` — Kalenji, Jan 10 '19 at 16:20

score 1 · Accepted Answer · answered Jan 10 '19 at 16:42

You want to use GROUP BY to make one row for each date in your d derived table.

Then join d to the t1 table where the d.dt is between the open and close dates.

SELECT
    d.dt, COUNT(*) AS open_items
FROM
(
    SELECT '2018-12-17' AS dt UNION ALL
    SELECT '2018-12-18' UNION ALL
    SELECT '2018-12-19' UNION ALL
    SELECT '2018-12-20' UNION ALL
    SELECT '2018-12-21' UNION ALL
    SELECT '2018-12-22' UNION ALL
    SELECT '2018-12-23' UNION ALL
    SELECT '2018-12-24'
) d
LEFT JOIN T1 ON d.dt BETWEEN t1.Open_Date and t1.Close_Date
GROUP BY d.dt;

Output:

+------------+------------+
| dt         | open_items |
+------------+------------+
| 2018-12-17 |          1 |
| 2018-12-18 |          3 |
| 2018-12-19 |          2 |
| 2018-12-20 |          4 |
| 2018-12-21 |          4 |
| 2018-12-22 |          4 |
| 2018-12-23 |          3 |
| 2018-12-24 |          3 |
+------------+------------+

Thank you! Follow up question. If I did not have all the SELECTS with UNION ALL can I achieve the same results? I mean, if only T1 is present. — Kalenji, Jan 11 '19 at 11:56
If you do that, you'll only get dates that are present in T1. If there are any dates with zero open items, they won't appear as the date with 0 items, the row will be missing from the result set. Often I create a reference table for the dates, with one row per date. A table that covers 10 years worth of dates is still at most 3,653 rows, which is a small table by modern standards. — Bill Karwin, Jan 11 '19 at 13:33

MySQL 5.5 - count open items per day

1 Answers1

Linked