Post reply

Logic similar to EXIST to make all values the same if it meets a criteria

  • July 1, 2024 at 6:24 pm

    #4432762

    Hello everyone,

    I have a question. Please let me know if someone has any ideas.

    I need a logic with maybe EXIST(?) to check separately if for one Chain there is a field in REPLENTYPE with a value ‘SINGLE’

    – I need all fields for this UPC at a specific Masterchainname to be ‘REPLENISHABLE’ for all STARTDATE weeks. So it should be based only on UPC#, and MASTERCHAINNAME.

    SELECT DISTINCT UPC, STARTDATE, [MASTERCHAINNAME], REPLENTYPE,
    CASE WHEN [REPLENTYPE] IS NULL THEN 'Not in CKB' 
    WHEN [REPLENTYPE] = 'NONE' THEN 'Non Replenishable'
    ELSE 'Replenishable' END AS 'Replentype'

    Screenshot

  • July 1, 2024 at 6:36 pm

    #4432782

    EXISTS will do it. But as you have not provided consumable data, I can't code it for you.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 1, 2024 at 9:58 pm

    #4432821 

    SELECT DISTINCT UPC, STARTDATE, [MASTERCHAINNAME], REPLENTYPE,
    CASE WHEN [REPLENTYPE] IS NULL THEN 'Not in CKB' 
    WHEN [REPLENTYPE] = 'NONE' THEN 'Non Replenishable'
    ELSE 'Replenishable' END AS 'Replentype'
    FROM dbo.tablename t1
    WHERE NOT EXISTS(SELECT * FROM dbo.tablename t2 ON t2.UPC = t1.UPC AND t2.MASTERCHAINNAME = t1.MASTERCHAINNAME AND t2.REPLENTYPE <> 'Replenishable')

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply