MySQL 5.5 - count open items per day with condition

Question

I have been working on one item that is easy in excel and I can not do it in MySQL. This is a follow up question with new values and new requirements to this one: MySQL 5.5 - count open items per day

So, again I have got the same table in excel and I want to achive Count_open in MySQL. Excel's formula is =COUNTIFS($A$2:$A$30000,"<="&E2,$B$2:$B$30000,">="&E2)

So, in my T1 table I have got two dates, open and close and I want to calculate how many where open per date.

Previously I used temp table for the last 7 days but this time I need to just stick to T1 table.

To get T1 table, I use the following code:

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');

So far I have tried below code but it does not yield the correct results.

SELECT T1.Open_Date, count(*) FROM T1
WHERE
    T1.Open_Date>='2018-12-01' and t1.Close_Date <='2019-03-17'

GROUP BY T1.Open_Date;

I am lost at the moment and your help is much needed!

score 2 · Accepted Answer · edited Jan 12 '19 at 15:00

The difference between Excel and a database is that you have manually generated the dates first in Excel. You could do that too in mysql and write a list of queries each for every date. That is basically the same as you do in your excel.

But luckily mysql isn't excel, so we can automate that. First we must generate a interval of dates. There is a big thread about that here: generate days from date range.

Then we just have to group the valid dates and voila:

Select a.Date, Count(t.ID)
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a union all select 1 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) as a
    cross join (select 0 as a union all select 1 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) as b
    cross join (select 0 as a union all select 1 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) as c
    cross join (select 0 as a union all select 1 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) as d
) a, T1 t
where a.Date between '2018-12-01' and '2019-03-17' 
and a.Date between  t.Open_Date and t.Close_Date
group by a.Date

MySQL 5.5 - count open items per day with condition

1 Answers1