Finding the status of the SKU items manufactured

Transact-SQL
1

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..

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

Can you please help me here

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

2

Great job on the DDL & DML. Can you remove the redundant data and explanation. one can get lost in the details while very good

3

Hi @yosiasz ...Thanks for getting back!..There is no redundant data here... For instance 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

4

@yosiasz : To summarize

  1. 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

2)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

  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...

5

Any help on this thread will be really appreciated! :smiley:

6

To give a general perspective ..The mfg table has the SKU'S (combination of mmodel and srno) that get manufactured on a particular day say 03/14..If the same SKU was present in the delay table on 03/13 then it means it means it has come off delay (out of delay) on 03/14 and got manufacturing completed..if the SKU that got manufacturing completed on 03/14 was not present in delay table on 03/13 then it implies that it is good stock since it was not in delayed status on the previous day...

Now on the same day of manufacturing completed there are other SKU'S that get still remain in the delayed on the same day in the delayed file due to quality issues ..Now these SKU'S can be present only in the same date as the current manufacturing date in comparison in the delay file and not on the immediate previous date in the delay table..If this is the case then it is new delay ...if the SKU is not present in the mfg table and is present in delay table on the same date and also on the immediate previous date in the delay table then it is off delay too

7

hi

are you still looking for a solution to this ?

if you are I Can Try

8

Hi Harishgg1, Great to hear from you...I have almost solved this problem...Thanks for asking!