Mysql – Row for each date, start and end

datedatetimeMySQLquery

I have a table that has two columns, startDate and endDate. Each day has its own capacity. Between these dates, user can get from 1 to n items. I am going to get number of items remaining for each day and show them to user. Is there any way I handle it with one query?

I am using spittet/php-mysql docker image and MySQL Version is 5.5.

id    startDate     endDate      capacity
----------------------------------------------------
1     2019-05-01     2019-06-01     8
2     2019-06-21     2019-07-01     13
3     2019-07-11     2019-08-01     4

And the other table stored user data

id        user_id      item_id      date                number
----------------------------------------------------------------
1             1            1       2019-05-03             1
2             2            3       2019-07-13             4

Desired output:

item_id            date           available_items
-------------------------------------------------------
1                  2019-05-01           8
1                  2019-05-02           8
1                  2019-05-03           7 <<-----------
1                  2019-05-04           8
1                  2019-05-05           8
...
1                  2019-05-31           8
2                  2019-06-21           13
2                  2019-06-22           13
...
2                  2019-06-30           13
3                  2019-07-11           4
3                  2019-07-12           4
3                  2019-07-13           0 <<-----------
3                  2019-07-14           4
...
3                  2019-07-31           4

Best Answer

I suggest you create a calendar table with a couple of years of data. I'll just use one with sufficiently many rows for your example:

CREATE TABLE CALENDAR (d date not null primary key);
INSERT INTO CALENDAR (d) VALUES ('2019-05-01'),...,('2019-08-01');

While we are at it, let's create some usable sample data instead of ascii-art tables:

create table items 
( item_id int not null primary key
, startdate date not null
, enddate date not null
, capacity int not null);

insert into items (item_id, startdate, enddate, capacity)
values (1, '2019-05-01', '2019-06-01', 8)
     , (2, '2019-06-21', '2019-07-01', 13)
     , (3, '2019-07-11', '2019-08-01', 4);

To determine the capacity for each date:

select cal.d, i.item_id, coalesce(i.capacity, 0) as capacity
from calendar cal
left join items i
    on cal.d between i.startdate and i.enddate;

Now you can subtract your userdata from that, perhaps easiest to do with a sub-select

select cal.d, i.item_id
     , coalesce(i.capacity, 0) - coalesce((select sum(number)
                                           from userdata u
                                           where u.dt = cal.d
                                             and u.item_id = i.item_id),0) as availible_items
from calendar cal
left join items i
    on cal.d between i.startdate and i.enddate;
Related Question