Precisely Enterworks

 View Only

  • 1.  Assistance With A SQL Query

    Posted 09-21-2022 11:04
    Hi.

    I'm still learning about SQL and need assistance with something.

    • I have a one to many relationship from our SKU Repository (SKU_STAGING) to our Area Repository (AREA_STAGING) using SKU Number as the join.
    • Area consists of up to thirty records with varying status values. When all of those status fields turn to 'Discontinued', it would be beneficial for us to receive an email from PIM that tells us that the SKU is 100% discontinued and can be removed from various hierarchies.
    • If one status still is not set to Discontinued, it can't be in the report that I want to generate.
    Any ideas? Here's my start.

    SELECT s.internalRecordId
    FROM SKU_STAGING s
    JOIN AREA_STAGING a ON s.SKU_Number = a.SKU_Number
    WHERE a.Status = 'Discontinued'

    Thanks in advance!

    Best,
    -Jon

    ------------------------------
    Jonathan Varo | Manager - PIM Group
    Fender Musical Instruments Corporation | 480-845-5823
    ------------------------------


  • 2.  RE: Assistance With A SQL Query

    Posted 09-22-2022 09:29
    Hi Jon,

    Try this:
    SELECT s.internalRecordId
    FROM SKU_STAGING s
    JOIN (SELECT DISTINCT a.SKU_Number FROM AREA_STAGING
    WHERE a.Status != 'Discontinued') a ON s.SKU_Number = a.SKU_Number

    The inner select of 
    (SELECT DISTINCT a.SKU_Number FROM AREA_STAGING WHERE a.Status != 'Discontinued') just tells the query to grab all SKU_Numbers which do NOT have a Status of Discontinued.  If a SKU has both a status of, for instance 'Active' and 'Discontinued'  it will still show on the list since the SELECT DISTINCT will simply give you a list of all of the SKU's which are in the output.

    I couldn't actually test the SQL, but I'm pretty confident it will work.


    ------------------------------
    Rick Spencer
    PIM Systems Administrator
    Animal Supply Company
    rick.spencer@animalsupply.com
    ------------------------------

    Original Message


  • 3.  RE: Assistance With A SQL Query

    Posted 09-23-2022 09:31
    I will try it out now... Thanks so much Rick. I'll report back.

    ------------------------------
    Jonathan Varo | Manager - Master Data and PIM Teams
    Fender Musical Instruments Corporation | (480) 845-5826
    ------------------------------

    Original Message


  • 4.  RE: Assistance With A SQL Query

    Posted 09-23-2022 09:57
    Thanks again Rick!​ Unfortunately, I ran the query but it was still finding SKUs with mixed results. Here is one SKU that it identified yet still had Active Area records:

    SKU Number Area Status
    7709946000 CAN_DEALER Active
    7709946000 INTL_DEALER Active
    7709946000 US_DEALER Active
    7709946000 JPN_DEALER Discontinued
    7709946000 JPN_CONSUMER Discontinued
    7709946000 AUS_DEALER Discontinued
    7709946000 INTL-APAC_DEALER Active
    7709946000 AUS_CONSUMER Active

    Just to clarify, I need to identify SKU Numbers that have Statuses in Area that all equal "Discontinued".

    SKU Number Area Status
    2160100410 US_DEALER Discontinued
    2160100410 MX_DEALER Discontinued
    2160100410 INTL_DEALER Discontinued
    2160100410 CAN_DEALER Discontinued
    2160100410 US_CONSUMER Discontinued
    2160100410 INTL-APAC_DEALER Discontinued


    ------------------------------
    Jonathan Varo | Manager - Master Data and PIM Teams
    Fender Musical Instruments Corporation | (480) 845-5826
    ------------------------------

    Original Message


  • 5.  RE: Assistance With A SQL Query

    Posted 09-23-2022 10:20
    I come up with this JOIN and, so far, it's working:

    JOIN Area_Staging a ON a.SKU_Number = s.SKU_Number AND a.Status = 'Discontinued' AND a.Status NOT LIKE 'Activ%'

    FYI... The NOT LIKE is finding all records that match both ACTIVE and ACTIVE_BEING DISCONTINUED statuses.



    ------------------------------
    Jonathan Varo | Manager - Master Data and PIM Teams
    Fender Musical Instruments Corporation | (480) 845-5826
    ------------------------------

    Original Message


  • 6.  RE: Assistance With A SQL Query

    Posted 09-23-2022 15:17

    Hey Jon,

    Another SQL you can try is:

    SELECT s.internalRecordId
    FROM SKU_STAGING s
    LEFT JOIN AREA_STAGING a ON s.SKU_Number = a.SKU_Number and a.Status != 'Discontinued'
    WHERE a.SKU_Number is null

    This returns all records from SKU_STAGING and includes records from AREA_STAGING if the status is not 'Discontinued'. If all related AREA_STAGING records for the SKU are Discontinued, then none return and the final where clause filters for these specifically. A potential gap in this is if there are no records at all in AREA_STAGING for your SKU. These will also turn up in this query's results.

    ​

    ------------------------------
    Ray Smith | Manager, Data Engineering
    UnitedHealth Group
    ------------------------------

    Original Message


  • 7.  RE: Assistance With A SQL Query

    Posted 09-23-2022 19:51
    Thanks! This is definitely working for the SKU level.

    ------------------------------
    Jonathan Varo | Manager - Master Data and PIM Teams
    Fender Musical Instruments Corporation | (480) 845-5826
    ------------------------------

    Original Message