0

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
2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.