Finding the status of the SKU items manufactured

Hellothere8028 821 Reputation points
2022-04-25T22:12:51.107+00:00

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

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

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

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-04-27T06:46:18.943+00:00

    Hi @Hellothere8028
    Thank you for your explanation and summary, and please check this query: 

    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=DATEADD(DAY,1,D.snapdate) 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'  
EXCEPT  
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=DATEADD(DAY,1,D2.snapdate) AND D1.mmodel=D2.mmodel  
)  
ORDER BY Dated

    Best regards,
    LiHong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Hellothere8028 821 Reputation points
    2022-05-23T20:29:24.547+00:00

    @LiHong-MSFT : Thank you so much!..Really appreciate your help!

    0 comments