MySQL Forums
Forum List  »  General

My SQL Query to find the status of the SKU items manufactured
Posted by: Ozark Ram
Date: April 25, 2022 04:10PM

Hope you are doing well!..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

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

Options: ReplyQuote


Subject
Written By
Posted
My SQL Query to find the status of the SKU items manufactured
April 25, 2022 04:10PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.