clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601434 fiddles created (47988 in the last week).

CREATE TABLE timetable ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, project_id INT(6) NOT NULL, item VARCHAR(35) NOT NULL, quantity INT(3) ZEROFILL NOT NULL, dt_start DATETIME, dt_END DATETIME ); INSERT INTO timetable (project_id,item,quantity,dt_start,dt_end) VALUES (1234, 'table', 50, '2021-01-10 10:00:00', '2021-01-12 10:00:00'), (1234, 'chair', 200, '2021-01-10 10:00:00', '2021-01-12 10:00:00'), (1234, 'light', 2, '2021-01-10 10:00:00', '2021-01-12 10:00:00'), (5678, 'table', 25, '2021-01-12 10:00:00', '2021-01-15 10:00:00'), (5678, 'light', 3, '2021-01-12 10:00:00', '2021-01-15 10:00:00'), (9876, 'table', 10, '2021-01-10 10:00:00', '2021-01-14 10:00:00'), (9876, 'chair', 100, '2021-01-10 10:00:00', '2021-01-14 10:00:00'), (2323, 'light', 10, '2021-01-12 10:00:00', '2021-01-15 10:00:00'); CREATE TABLE calendar (dt date not null primary key); INSERT INTO calendar (dt) VALUES ('2021-01-9'),('2021-01-10'),('2021-01-11'),('2021-01-12'), ('2021-01-13'),('2021-01-14'),('2021-01-15'),('2021-01-16'), ('2021-01-17'),('2021-01-18'),('2021-01-19'),('2021-01-20');
 hidden batch(es)


SELECT t1.*, (SELECT CAST(SUM(t2.quantity) AS CHAR) FROM timetable t2 WHERE t2.item = t1.item AND ('2021-01-13 10:00:00' <= date(dt_END)) AND (date(dt_start) <= '2021-01-18 10:00:00') ) AS itm_sum FROM timetable t1 WHERE ('2021-01-13 10:00:00' <= date(dt_END)) AND (date(dt_start) <= '2021-01-18 10:00:00')
id project_id item quantity dt_start dt_END itm_sum
4 5678 table 025 2021-01-12 10:00:00 2021-01-15 10:00:00 35
5 5678 light 003 2021-01-12 10:00:00 2021-01-15 10:00:00 13
6 9876 table 010 2021-01-10 10:00:00 2021-01-14 10:00:00 35
7 9876 chair 100 2021-01-10 10:00:00 2021-01-14 10:00:00 100
8 2323 light 010 2021-01-12 10:00:00 2021-01-15 10:00:00 13
 hidden batch(es)