AnsweredAssumed Answered

SQL QUERY HELP - Trying to mine specific data card variable values, and filter latest issued versions

Question asked by Sean Flanagan on Jun 19, 2017
Latest reply on Jun 21, 2017 by Tim Webb

Hello,

I am trying to put together a table that will include the variables: PartNumber, SKU, Weight, and ConfigID. I created a new "TEST" database to play around with the data and I am getting very close, but my results are erroneous. They are displaying the same SKU on different part numbers which do not match what is in latest version in data card.

 

I found a way to filter latest revisions only by creating a bunch of tables and inner joining them together. Here is what I have so far:

 

 

 

//**Repopulates a table called weight multiple, which has all weights of all versions where weight was changed, of all configurations**//

DELETE FROM [TEST].[dbo].[Weight_Multiple]

Insert INTO [TEST].[dbo].[Weight_Multiple] (Config1,REV1,WGT1)

SELECT ConfigurationID,RevisionNo,ValueCache

FROM [NAPCO].[dbo].[VariableValue]

WHERE VariableID = 105 AND ConfigurationID !=
2 AND ConfigurationID !=3

ORDER BY ConfigurationID, RevisionNo

 

//**Repopulates a table called weight latest, that displays only config and revision, but filters only the highest revision instances of each configuration**//

DELETE FROM [TEST].[dbo].[Weight_Latest]

Insert INTO [TEST].[dbo].[Weight_Latest] (Config2,MAXREV2)

SELECT ConfigurationID, MAX(RevisionNo) AS LATEST

FROM [NAPCO].[dbo].[VariableValue]

WHERE VariableID = 105 AND ConfigurationID !=
2 AND ConfigurationID !=3

GROUP BY ConfigurationID

ORDER BY ConfigurationID, LATEST

 

//**Repopulates a table called weight final, which inner joins weight multiple with weight latest only where config# and rev# match, and displays config, weight, and rev***//

DELETE FROM [TEST].[dbo].[Weight_Final]

Insert INTO [TEST].[dbo].[Weight_Final] (Config3,MAXREV,WGT)

SELECT Config1, REV1, WGT1

FROM [TEST].[dbo].[Weight_Multiple]

INNER JOIN [TEST].[dbo].[Weight_Latest]

ON [TEST].[dbo].[Weight_Multiple].[Config1]
= [TEST].[dbo].[Weight_Latest].[Config2]

AND [TEST].[dbo].[Weight_Multiple].[REV1]
= [TEST].[dbo].[Weight_Latest].[MAXREV2]

ORDER BY Config1

 

//**Repopulates a table called partno multiple, which has all partnumbers of all versions where partnumber was changed, of all configurations**//

DELETE FROM [TEST].[dbo].[PartNO_Multiple]

Insert INTO [TEST].[dbo].[PartNO_Multiple] (PartNo10,REVNO10,ConfigID10)

SELECT [ValueCache],[RevisionNo],[ConfigurationID]

FROM [NAPCO].[dbo].[VariableValue]

WHERE VariableID = 57 AND ConfigurationID !=2

AND (ValueCache LIKE '0_____-__')

ORDER BY ValueCache

 

//**Repopulates a table called partno latest which displays only partno and revision, but filters only the highest revision instances of each configuration**//

DELETE FROM [TEST].[dbo].[PartNO_Latest]

Insert INTO [TEST].[dbo].[PartNO_Latest] (PartNo11,MAXREV11)

SELECT DISTINCT
ValueCache, MAX(RevisionNo) AS LATEST

FROM [NAPCO].[dbo].[VariableValue]

WHERE VariableID = 57 AND ConfigurationID !=
2

AND (ValueCache LIKE '0_____-__')

GROUP BY ValueCache

ORDER BY ValueCache

 

 

 

 

 

//**Repopulates a table called partno final, which inner joins partno multiple with partno latest only where partno and rev# match, and displays config, partno, and rev***//

 

 

 

 

 

 

 

 

 

DELETE FROM [TEST].[dbo].[PartNo_Final]

Insert INTO [TEST].[dbo].[PartNo_Final] (PartNo4,ConfigID4,Rev4)

SELECT DISTINCT PartNo10,ConfigID10,REVNO10

FROM [TEST].[dbo].[PartNO_Multiple]

INNER JOIN [TEST].[dbo].[PartNO_Latest]

ON [TEST].[dbo].[PartNO_Multiple].[PartNo10]
= [TEST].[dbo].[PartNO_Latest].[PartNo11]

AND [TEST].[dbo].[PartNO_Multiple].[REVNO10]
= [TEST].[dbo].[PartNO_Latest].[MAXREV11]

ORDER BY PartNo10

 

//**Repopulates a table called SKU multiple**//

DELETE FROM [TEST].[dbo].[SKU_Multiple]

Insert INTO [TEST].[dbo].[SKU_Multiple] (CONFIGURATIONID,SKU,REV)

SELECT DISTINCT
ConfigurationID,ValueCache,RevisionNo

FROM [NAPCO].[dbo].[VariableValue]

WHERE VariableID = 127 AND ConfigurationID !=
2

AND (ValueCache LIKE '1_____________' OR
ValueCache LIKE '3_____________')

ORDER BY ConfigurationID, RevisionNo

 

 

 

 

//***Repopulates SKU_Latest***//

DELETE FROM [TEST].[dbo].[SKU_Latest]

Insert INTO [TEST].[dbo].[SKU_Latest] (CONFIGID,Latest)

SELECT DISTINCT
ConfigurationID,MAX(RevisionNo) AS LATEST

FROM [NAPCO].[dbo].[VariableValue]

WHERE VariableID = 127 AND ConfigurationID !=
2

AND (ValueCache LIKE '1_____________' OR
ValueCache LIKE '3_____________')

GROUP BY ConfigurationID

ORDER BY ConfigurationID

//***Combines SKU_Multiple and SKUlatest through inner join to create SKU_Final***//

 

DELETE FROM [TEST].[dbo].[SKU_Final]

Insert INTO [TEST].[dbo].[SKU_Final] (CONFIG_ID,SAP_NUMBER)

SELECT DISTINCT
CONFIGURATIONID,SKU FROM
[TEST].[dbo].[SKU_Multiple]

INNER JOIN [TEST].[dbo].[SKU_Latest]

ON [TEST].[dbo].[SKU_Multiple].[CONFIGURATIONID] = [TEST].[dbo].[SKU_Latest].[CONFIGID]

AND [TEST].[dbo].[SKU_Multiple].[REV]
= [TEST].[dbo].[SKU_Latest].[Latest]

 

//**Combines Final SKU with Final PartNumber, Displays columns SKU,PartNo,ConfigID**//

DELETE FROM [TEST].[dbo].[PartNoxSKU_Final]

Insert INTO [TEST].[dbo].[PartNoxSKU_Final]

(PartNo5,SKU5,ConfigID5)

SELECT DISTINCT PartNo4, SAP_NUMBER,
ConfigID4

FROM [TEST].[dbo].[PartNo_Final]

JOIN [TEST].[dbo].[SKU_Final]

ON [TEST].[dbo].[PartNo_Final].[ConfigID4]
= [TEST].[dbo].[SKU_Final].[CONFIG_ID]

ORDER BY PartNo4

 

//**Combines previous table with Final weight table, displays columns PartNumber, ConfigID, Weight, and SKUNumber, populates into a table called FINAL RESULT***//

DELETE FROM [TEST].[dbo].[Final_Result]

Insert INTO [TEST].[dbo].[Final_Result]

(PartNumber,ConfigID,WGT_LB,SKUNUMBER)

SELECT DISTINCT PartNo5, ConfigID5, WGT, SKU5

FROM [TEST].[dbo].[PartNoxSKU_Final]

JOIN [TEST].[dbo].[Weight_Final]

ON [TEST].[dbo].[PartNoxSKU_Final].[ConfigID5]
= [TEST].[dbo].[Weight_Final].[Config3]

ORDER BY PartNo5

 

 

END.

 

 

Please help my find where my code went wrong!! I know the partnumber tables are all correct so I can at least eliminate that being the problem. I have attached a spreadsheet of my results, and as you can see in the SKU column, the parts highlighted in red are duplicate values, where one is correct and the other is not.

 

 

Thanks and best regards,

-Sean

 

 

 

 

Attachments

Outcomes