1

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

6
  • Can @verace help? Jan 20, 2021 at 16:49
  • What is the specific problem you are having? Getting input from the user? Jan 20, 2021 at 16:55
  • Nope, the user inputs the dates. The issue is I am not getting the inventory counts correctly once I add dates into it. Jan 20, 2021 at 17:03
  • When I enter the 2021-01-10 through 2021-01-12, I get the result I want. When I enter 2021-01-13 through 2021-01-18, I get a zero result set Jan 20, 2021 at 17:06
  • 1
    What are you using for your filtering criteria?
    – bbaird
    Jan 20, 2021 at 17:09

1 Answer 1

1

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

3
  • Eureka! I think you did it... again. I am sort of think I understand what you did, but it is way above my coding skills. Thank you!! Jan 20, 2021 at 20:07
  • it takes time, to learn such things and it helps to see the ansets here and on SO how they solve problems
    – nbk
    Jan 20, 2021 at 20:30
  • Well put. Between the code I got yesterday and what I got today, has been super helpful!! Have a great week. Jan 20, 2021 at 20:57

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.