question

ArunChandramouli-6978 avatar image
0 Votes"
ArunChandramouli-6978 asked ArunChandramouli-6978 answered

Finding the status of the SKU items manufactured

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

sql-server-transact-sql
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ArunChandramouli-6978
There seams to be some difference between your logical description and your desired output.
For example,

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)

For date '3/23/2021',i can found mmodel 'NM213','OP1231','MB321' and 'XC2312' both in status table and mfg table.However ,regarding the remaining SKU'S in the delay table, i find 'MX201' in date '3/23/2021' which doesn't show up in your output table.

In addition,for date '3/24/2021',i find mmodel 'LZ231' in mfg table ,but it doesn't show up in output.

Could you please modify your output table.Or maybe I misunderstood, please explain that.

Best regards,
LiHong

0 Votes 0 ·

Hi @LiHongMSFT-3908 : Thanks for getting back! To answer your question..There is one more logic that I dint add which is

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 - which should answer your question on egarding the remaining SKU'S in the delay table, i find 'MX201' in date '3/23/2021' which doesn't show up in your output table.


Also with regard to your question on In addition,for date '3/24/2021',i find mmodel 'LZ231' in mfg table ,but it doesn't show up in output - Yes this was a miss from my end...I have added it...

0 Votes 0 ·

@LiHongMSFT-3908 : 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...

0 Votes 0 ·
Show more comments
LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered LiHongMSFT-3908 commented

Hi @ArunChandramouli-6978
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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @LiHongMSFT-3908 , For some reason I am getting only the skucompleted and goodstock as 1 ..I am not getting the offdelay and newdelay values as 1 at all


0 Votes 0 ·
LiHongMSFT-3908 avatar image LiHongMSFT-3908 ArunChandramouli-6978 ·

Hi @ArunChandramouli-6978
That's kind weird...Since I tested the query with the sample datas you given,and i got the output correctlly.I guess maybe you modified some code.
My output:
198672-image.png

Best regards,
LiHong

0 Votes 0 ·
image.png (18.6 KiB)
ArunChandramouli-6978 avatar image
0 Votes"
ArunChandramouli-6978 answered

@LiHongMSFT-3908 : Thank you so much!..Really appreciate your help!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.