'Mysql Query to find the status of the SKU items manufactured
I have a delay table (as below) where a SKU is identified by the combination of mmodel and srno and the snapdate.. A SKU present on a specific snapdate indicates the SKU was delayed on that date
Delay Table
mmodel Srno Snapdate
MX201 C12341 3/21/2021
NM213 L1234 3/21/2021
JK1231 K1561 3/21/2021
KL1234 MJ1234 3/21/2021
MX201 C12341 3/22/2021
NM213 L1234 3/22/2021
OP1231 JK123 3/22/2021
MB321 LK123 3/22/2021
MX201 C12341 3/23/2021
BX378 H1231 3/23/2021
LZ231 MD321 3/23/2021
LO3412 LP1231 3/23/2021
MX201 C12341 3/24/2021
BX378 H1231 3/24/2021
FE221 J6571 3/24/2021
Mfg table
mfgdate mmodel Srno
3/21/2021 JK123 K21310
3/21/2021 KL378 L8941
3/22/2021 JK1231 K1561
3/22/2021 KL1234 MJ1234
3/22/2021 KL918 S21367
3/23/2021 NM213 L1234
3/23/2021 OP1231 JK123
3/23/2021 MB321 LK123
3/23/2021 XC2312 M89321
3/24/2021 LZ231 MD321
3/24/2021 LO3412 LP1231
3/24/2021 KL5612 D3489
The above table has the mfgdate (date when the SKU got manufactured). Now I want to create a status table (as below) and ignore the first date in the above two tables and start from the second date.
Status output table
Date mmodel Srno SKUComplete Goodstock offdelay NewDelay
3/22/2021 JK1231 K1561 1 0 1 0
3/22/2021 KL1234 MJ1234 1 0 1 0
3/22/2021 KL918 S21367 1 1 0 0
3/22/2021 OP1231 JK123 0 0 0 1
3/22/2021 MB321 LK123 0 0 0 1
3/23/2021 NM213 L1234 1 0 1 0
3/23/2021 OP1231 JK123 1 0 1 0
3/23/2021 MB321 LK123 1 0 1 0
3/23/2021 XC2312 M89321 1 1 0 0
3/23/2021 BX378 H1231 0 0 0 1
3/23/2021 LZ231 MD321 0 0 0 1
3/23/2021 LO3412 LP1231 0 0 0 1
3/24/2021 LO3412 LP1231 1 0 1 0
3/24/2021 KL5612 D3489 1 1 0 0
3/24/2021 FE221 J6571 0 0 0 1
The above table is got by looking up the mfg table first, and the SKU present in the mfg table would be marked as SKUComplete 1 ..Now the same SKU should be looked up in the delay table on the previous date (Mfgdate-1)..If the SKU is present in the previous date in the delay table then offdelay would become 1 otherwise goodstock would become 1
After completing all the SKU'S in the mfg table for a particular manufacturing date the remaining SKU'S should be looked up in the delay table for the same date (non matching SKU'S) and they should be entered in the Status table with New delay as 1..Please find the DDL for the tables below
one of the other things to take care of is that if a SKU is not available in the mfg table on a particular mfg date and the SKU is only in the delay table on that specific mfgdate and is also available on the immediate previous delay date then that SKU will not be accounted for on that date delay table
Create table delay
(mmodel varchar(40),
srno varchar(40),
snapdate date)
insert into delay values
('MX201','C12341','3/21/2021'),
('NM213','L1234','3/21/2021'),
('JK1231','K1561','3/21/2021'),
('KL1234','MJ1234','3/21/2021'),
('MX201','C12341','3/22/2021'),
('NM213','L1234','3/22/2021'),
('OP1231','JK123','3/22/2021'),
('MB321','LK123','3/22/2021'),
('MX201','C12341','3/23/2021'),
('BX378','H1231','3/23/2021'),
('LZ231','MD321','3/23/2021'),
('LO3412','LP1231','3/23/2021'),
('MX201','C12341','3/24/2021'),
('BX378','H1231','3/24/2021'),
('FE221','J6571','3/24/2021')
**Mfg table**
Create table mfg
(mfgdate date),
mmodel varchar(40),
srno varchar(40),
)
insert into mfg values
('3/21/2021','JK123','K21310'),
('3/21/2021','KL378','L8941'),
('3/22/2021','JK1231','K1561'),
('3/22/2021','KL1234','MJ1234'),
('3/22/2021','KL918','S21367'),
('3/23/2021','NM213','L1234'),
('3/23/2021','OP1231','JK123'),
('3/23/2021','MB321','LK123'),
('3/23/2021','XC2312','M89321'),
('3/24/2021','LZ231','MD321'),
('3/24/2021','LO3412','LP1231'),
('3/24/2021','KL5612','D3489')
**Output table**
create table output
(Dated date,
mmodel varchar(40),
srno varchar(40),
skucomplete int,
goodstock int,
offdelay int,
newdelay int
)
inert into output values
('3/22/2021','JK1231','K1561','1','0','1','0'),
('3/22/2021','KL1234','MJ1234','1','0','1','0'),
('3/22/2021','KL918','S21367','1','1','0','0'),
('3/22/2021','OP1231','JK123','0','0','0','1'),
('3/22/2021','MB321','LK123','0','0','0','1'),
('3/23/2021','NM213','L1234','1','0','1','0'),
('3/23/2021','OP1231','JK123','1','0','1','0'),
('3/23/2021','MB321','LK123','1','0','1','0'),
('3/23/2021','XC2312','M89321','1','1','0','0'),
('3/23/2021','BX378','H1231','0','0','0','1'),
('3/23/2021','LZ231','MD321','0','0','0','1'),
('3/23/2021','LO3412','LP1231','0','0','0','1'),
('3/24/2021','LO3412','LP1231','1','0','1','0'),
('3/24/2021','KL5612','D3489','1','1','0','0'),
('3/24/2021','FE221','J6571','0','0','0','1')
('3/24/2021','LZ231','MD321','1','0','1','0')
To summarize
The SKU from the mfg table and date should be taken and if the same SKU is present in the immediate previous date in the delay table then its status would be complete -1 and offdelay -1
The SKU from the mfg table and date should be taken and if the same SKU is not present in the immediate previous date in the delay table then its status would be complete -1 and goodstock-1
The SKU that is not present in mfg table on a specific date but is present in the delay table on that specific date (but the same SKU is not present in the immediate previous date in the delay table then its status would be just new delay -1
4)The SKU that is not present in mfg table on a specific date but is present in the delay table on that specific date (but the same SKU is present in the immediate previous date in the delay table then that SKU won't be in the output at all for that date...
Query Tried
SELECT M.mfgdate AS Dated,M.mmodel AS mmodel,M.srno AS srno
,1 AS skucomplete
,CASE WHEN D.mmodel IS NULL THEN 1 ELSE 0 END AS goodstock
,CASE WHEN D.mmodel IS NOT NULL THEN 1 ELSE 0 END AS offdelay
,0 AS newdelay
FROM mfg M LEFT JOIN delay D ON M.mfgdate=DATE_ADD(D.snapdate,INTERVAL 1 DAY) AND M.mmodel=D.mmodel
WHERE M.mfgdate>'3/21/2021'
UNION ALL
(
SELECT snapdate,mmodel,srno,0 AS skucomplete,0 AS goodstock,0 AS offdelay,1 AS newdelay
FROM delay
WHERE snapdate>'3/21/2021'
AND NOT EXISTS(
SELECT D1.snapdate,D1.mmodel,D1.srno,0 AS skucomplete,0 AS goodstock,0 AS offdelay,1 AS newdelay
FROM delay D1 JOIN delay D2 ON D1.snapdate=DATE_ADD(D2.snapdate,INTERVAL 1 DAY) AND D1.mmodel=D2.mmodel)
)
ORDER BY Dated
From the result of the above query I am getting values for only the skucompleted and goodstock as 1 ..I am not getting any values for offdelay and newdelay..
Thanks, Arun
Solution 1:[1]
It seems like
SELECT mmodel, Srno, MAX(Snapdate) AS latest_delay
FROM delays
GROUP BY mmodel, Srno;
would be handy in providing the latest_delay for any mmodel+srno. Maybe include that somehow in the ultimate query?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Rick James |