MySQL – Inventory Count Per Date Range

MySQL

I am trying to create a report that shows how many items are available within a specific date range, entered by the user. It should coordinate with requested variable start and end dates…

Start: 2021-01-10 / End: 2021-01-15 results should be…

1234 | table | 050 | 2021-01-10 | 2021-01-12 | 085
1234 | chair | 200 | 2021-01-10 | 2021-01-12 | 300
1234 | light | 002 | 2021-01-10 | 2021-01-12 | 005
5678 | table | 025 | 2021-01-12 | 2021-01-15 | 085
5678 | light | 003 | 2021-01-12 | 2021-01-15 | 005
9876 | table | 010 | 2021-01-10 | 2021-01-14 | 085
9876 | chair | 100 | 2021-01-10 | 2021-01-14 | 300
2323 | light | 010 | 2021-01-12 | 2021-01-15 | 010

Start: 2021-01-13 / End: 2021-01-18 results should be…

5678 | table | 025 | 2021-01-12 | 2021-01-15 | 035
5678 | light | 003 | 2021-01-12 | 2021-01-15 | 013
9876 | table | 010 | 2021-01-10 | 2021-01-14 | 035
9876 | chair | 100 | 2021-01-10 | 2021-01-14 | 100
2323 | light | 010 | 2021-01-12 | 2021-01-15 | 013

SELECT 
  t1.*, 
  (SELECT CAST(SUM(t2.quantity) AS CHAR)
   FROM timetable t2 
   WHERE t2.item = t1.item) AS itm_sum
FROM timetable t1

SELECT 
  t1.*, 
  (SELECT CAST(SUM(t2.quantity) AS CHAR)
    FROM timetable t2 
    WHERE t2.item = t1.item) AS itm_sum
FROM timetable t1
 WHERE ('2021-01-13 10:00:00' <= dt_END) AND

(dt_start <= '2021-01-18 10:00:00')

I am getting incorrect results.

The beginning Fiddle is here….
https://www.db-fiddle.com/f/o5WtkgisvjN4VvwwKai3BP/3#&togetherjs=JLKJ4PjgsU

Best Answer

It is quite simole your inner SELECT counts every row that it gets, from timetable so you have to rstrict also the inner SEELCT

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

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

db<>fiddle here

Related Question