Post reply

Best way to retrieve this sum?

  • February 25, 2016 at 11:36 am

    #325069

    Hi,

    Let me start by providing a simplified version of my tables and query:

    CREATE TABLE #Warehouse (

    WarehousePk INT,

    WarehouseNumber VARCHAR(3)

    )

    CREATE TABLE #Item (

    ItemPk INT,

    ItemDescription VARCHAR(10)

    )

    CREATE TABLE #WarehouseItem (

    WarehouseItemPk INT,

    WarehouseFk INT,

    ItemFk INT

    )

    INSERT INTO #Warehouse (WarehousePk, WarehouseNumber) VALUES (1,'100')

    INSERT INTO #Warehouse (WarehousePk, WarehouseNumber) VALUES (2,'200')

    INSERT INTO #Item (ItemPk, ItemDescription) VALUES (50,'Item A')

    INSERT INTO #Item (ItemPk, ItemDescription) VALUES (51,'Item B')

    INSERT INTO #WarehouseItem (WarehouseItemPk, WarehouseFk, ItemFk) VALUES (1,1,50)

    INSERT INTO #WarehouseItem (WarehouseItemPk, WarehouseFk, ItemFk) VALUES (2,2,50)

    INSERT INTO #WarehouseItem (WarehouseItemPk, WarehouseFk, ItemFk) VALUES (3,2,50)

    INSERT INTO #WarehouseItem (WarehouseItemPk, WarehouseFk, ItemFk) VALUES (4,2,51)

    SELECT WarehouseNumber, ItemDescription, Quantity = '?'

    FROM #Warehouse w

    JOIN #WarehouseItem wi ON wi.WarehouseFk = w.WarehousePk

    JOIN #Item i ON i.ItemPk = wi.ItemFk

    DROP TABLE #Warehouse

    DROP TABLE #Item

    DROP TABLE #WarehouseItem

    Basically, for the "Quantity" field, I would like to replace the hard-coded "?" with the number of times the item is associated with the warehouse. So the resulting recordset from this select statement should yield a "1" for the first and fourth records, but a "2" for the second and third records, since "Item A" is associated with warehouse "200" twice.

    What is the best practice for obtaining this value?

    I greatly appreciate any assistance!

  • February 25, 2016 at 12:18 pm

    #1861214

    Using the OVER clause and the REPLICATE function, you could achieve this.

    SELECT WarehouseNumber,

    ItemDescription,

    Quantity = REPLICATE('?', COUNT(*) OVER( PARTITION BY WarehouseNumber, ItemDescription))

    FROM #Warehouse w

    JOIN #WarehouseItem wi ON wi.WarehouseFk = w.WarehousePk

    JOIN #Item i ON i.ItemPk = wi.ItemFk;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 25, 2016 at 1:13 pm

    #1861232

    Thank you!

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

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