By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.36 |
CREATE TABLE Projected_stock (
Material INTEGER ,
`Date` DATE ,
Stock INTEGER ,
Demand INTEGER ,
Supply INTEGER ,
Projected INTEGER ,
Target_Stock INTEGER ,
Overstock VARCHAR(20)
);
INSERT INTO Projected_stock VALUES
('123456','2024-06-24','60','0','0','60','24','Yes'),
('123456','2024-06-26','0','4','0','56','24','Yes'),
('123456','2024-06-27','0','4','0','52','24','Yes'),
('123456','2024-07-02','0','0','10','62','24','Yes'),
('123456','2024-07-04','0','0','10','72','24','Yes'),
('123456','2024-07-04','0','0','1','73','24','Yes'),
('123456','2024-07-04','0','1','0','72','24','Yes'),
('123456','2024-07-04','0','1','0','71','24','Yes'),
('123456','2024-07-04','0','4','0','67','24','Yes'),
('123456','2024-07-04','0','4','0','63','24','Yes'),
('123456','2024-07-05','0','4','0','59','24','Yes'),
('123456','2024-07-11','0','0','11','70','24','Yes'),
('123456','2024-07-13','0','1','0','69','24','Yes'),
('123456','2024-07-13','0','1','0','68','24','Yes'),
('123456','2024-07-13','0','1','0','67','24','Yes'),
('123456','2024-07-14','0','4','0','63','24','Yes'),
('123456','2024-07-16','0','4','0','59','24','Yes'),
('123456','2024-07-18','0','0','11','70','24','Yes'),
('123456','2024-07-18','0','4','0','66','24','Yes'),
('123456','2024-07-19','0','1','0','65','24','Yes'),
('123456','2024-07-19','0','1','0','64','24','Yes'),
('123456','2024-07-21','0','4','0','60','24','Yes'),
('123456','2024-07-21','0','1','0','59','24','Yes'),
('123456','2024-07-21','0','1','0','58','24','Yes'),
Records: 64 Duplicates: 0 Warnings: 0
Table 'fiddle.mytable' doesn't exist
-- and here the SQL your tried...
WITH CTE as
(
SELECT material, c.Date,
CASE
WHEN MIN(CASE WHEN Projected>48 THEN '1' ELSE '0' END)!=MAX(CASE WHEN Projected>48 THEN '1' ELSE '0' END) THEN '?'
ELSE MIN(CASE WHEN Projected>48 THEN '1' ELSE '0' END)
END as Overstock
FROM Projected_stock c
WHERE c.Date between c.Date AND DATE_ADD(c.Date, INTERVAL 28 DAY) /*dateadd(DAY, 28, c.Date)*/
GROUP BY c.material, c.Date
)
SELECT p.*, c.overstock
FROM CTE c
LEFT JOIN Projected_stock p ON p.Date=c.Date and c.material=p.material
| Material | Date | Stock | Demand | Supply | Projected | Target_Stock | Overstock | overstock |
|---|---|---|---|---|---|---|---|---|
| 123456 | 2024-06-24 | 60 | 0 | 0 | 60 | 24 | Yes | 1 |
| 123456 | 2024-06-26 | 0 | 4 | 0 | 56 | 24 | Yes | 1 |
| 123456 | 2024-06-27 | 0 | 4 | 0 | 52 | 24 | Yes | 1 |
| 123456 | 2024-07-02 | 0 | 0 | 10 | 62 | 24 | Yes | 1 |
| 123456 | 2024-07-04 | 0 | 4 | 0 | 63 | 24 | Yes | 1 |
| 123456 | 2024-07-04 | 0 | 4 | 0 | 67 | 24 | Yes | 1 |
| 123456 | 2024-07-04 | 0 | 1 | 0 | 71 | 24 | Yes | 1 |
| 123456 | 2024-07-04 | 0 | 1 | 0 | 72 | 24 | Yes | 1 |
| 123456 | 2024-07-04 | 0 | 0 | 1 | 73 | 24 | Yes | 1 |
| 123456 | 2024-07-04 | 0 | 0 | 10 | 72 | 24 | Yes | 1 |
| 123456 | 2024-07-05 | 0 | 4 | 0 | 59 | 24 | Yes | 1 |
| 123456 | 2024-07-11 | 0 | 0 | 11 | 70 | 24 | Yes | 1 |
| 123456 | 2024-07-13 | 0 | 1 | 0 | 67 | 24 | Yes | 1 |
| 123456 | 2024-07-13 | 0 | 1 | 0 | 68 | 24 | Yes | 1 |
| 123456 | 2024-07-13 | 0 | 1 | 0 | 69 | 24 | Yes | 1 |
| 123456 | 2024-07-14 | 0 | 4 | 0 | 63 | 24 | Yes | 1 |
| 123456 | 2024-07-16 | 0 | 4 | 0 | 59 | 24 | Yes | 1 |
| 123456 | 2024-07-18 | 0 | 4 | 0 | 66 | 24 | Yes | 1 |
| 123456 | 2024-07-18 | 0 | 0 | 11 | 70 | 24 | Yes | 1 |
| 123456 | 2024-07-19 | 0 | 1 | 0 | 64 | 24 | Yes | 1 |
| 123456 | 2024-07-19 | 0 | 1 | 0 | 65 | 24 | Yes | 1 |
| 123456 | 2024-07-21 | 0 | 1 | 0 | 57 | 24 | Yes | 1 |
| 123456 | 2024-07-21 | 0 | 1 | 0 | 58 | 24 | Yes | 1 |
| 123456 | 2024-07-21 | 0 | 1 | 0 | 59 | 24 | Yes | 1 |
| 123456 | 2024-07-21 | 0 | 4 | 0 | 60 | 24 | Yes | 1 |
| 123456 | 2024-07-25 | 0 | 4 | 0 | 62 | 24 | Yes | 1 |
| 123456 | 2024-07-25 | 0 | 0 | 9 | 66 | 24 | Yes | 1 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 107 | 24 | Yes | 1 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 98 | 24 | Yes | 1 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 89 | 24 | Yes | 1 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 80 | 24 | Yes | 1 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 71 | 24 | Yes | 1 |
| 123457 | 2024-06-24 | 30 | 0 | 0 | 30 | 24 | No | 0 |
| 123457 | 2024-06-26 | 0 | 4 | 0 | 26 | 24 | No | 0 |
| 123457 | 2024-06-27 | 0 | 4 | 0 | 22 | 24 | No | 0 |
| 123457 | 2024-07-02 | 0 | 0 | 10 | 32 | 24 | No | 0 |
| 123457 | 2024-07-04 | 0 | 4 | 0 | 33 | 24 | No | 0 |
| 123457 | 2024-07-04 | 0 | 4 | 0 | 37 | 24 | No | 0 |
| 123457 | 2024-07-04 | 0 | 1 | 0 | 41 | 24 | No | 0 |
| 123457 | 2024-07-04 | 0 | 1 | 0 | 42 | 24 | No | 0 |
| 123457 | 2024-07-04 | 0 | 0 | 1 | 43 | 24 | No | 0 |
| 123457 | 2024-07-04 | 0 | 0 | 10 | 42 | 24 | No | 0 |
| 123457 | 2024-07-05 | 0 | 4 | 0 | 29 | 24 | No | 0 |
| 123457 | 2024-07-11 | 0 | 0 | 11 | 40 | 24 | No | 0 |
| 123457 | 2024-07-13 | 0 | 1 | 0 | 37 | 24 | No | 0 |
| 123457 | 2024-07-13 | 0 | 1 | 0 | 38 | 24 | No | 0 |
| 123457 | 2024-07-13 | 0 | 1 | 0 | 39 | 24 | No | 0 |
| 123457 | 2024-07-14 | 0 | 4 | 0 | 33 | 24 | No | 0 |
| 123457 | 2024-07-16 | 0 | 4 | 0 | 29 | 24 | No | 0 |
| 123457 | 2024-07-18 | 0 | 4 | 0 | 36 | 24 | No | 0 |
| 123457 | 2024-07-18 | 0 | 0 | 11 | 40 | 24 | No | 0 |
| 123457 | 2024-07-19 | 0 | 1 | 0 | 34 | 24 | No | 0 |
| 123457 | 2024-07-19 | 0 | 1 | 0 | 35 | 24 | No | 0 |
| 123457 | 2024-07-21 | 0 | 1 | 0 | 27 | 24 | No | 0 |
| 123457 | 2024-07-21 | 0 | 1 | 0 | 28 | 24 | No | 0 |
| 123457 | 2024-07-21 | 0 | 1 | 0 | 29 | 24 | No | 0 |
| 123457 | 2024-07-21 | 0 | 4 | 0 | 30 | 24 | No | 0 |
| 123457 | 2024-07-25 | 0 | 4 | 0 | 32 | 24 | No | 0 |
| 123457 | 2024-07-25 | 0 | 0 | 9 | 36 | 24 | No | 0 |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 77 | 24 | ? | ? |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 68 | 24 | ? | ? |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 59 | 24 | ? | ? |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 50 | 24 | ? | ? |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 41 | 24 | No | ? |