1 Reply Latest reply on Jun 21, 2017 9:51 AM by Tim Webb

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

    Sean Flanagan

      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