Need help on stock export

  •  Hello everyone

    Think I posted in the wrong forum so I try again..

    I have the following.

    Have a table Item_Inbound containing

    Item_Code, Units, Sub_Units, Status

    Need to create a new table ExportStock containing

    Item_Code, StockFree, StockBlocked

    Example

    Item_Inbound

    Item_Code       Units      Sub_Units      Status

    A1200              100           10               1

    A1200               50            10               1

    A1200              100            10               2

    B1300               80             20               1

    C1400               60             12               2

    The table ExportStock should like

    Item_Code        StockFree           StockBlocked

    A1200                1500                      1000

    B1300                 1600                          0

    C1400                     0                         720

    I'm looking for a one stop solution to get from one table to the other...

    Can someone help me???

    Thanks

  • If I've understood you correctly, would this be the answer?

    INSERT INTO ExportStock (Item_Code, StockFree, StockBlocked)

    SELECT Item_Code,

    SUM(Units * Sub_Units * CASE WHEN Status = 1 THEN 1 ELSE 0 END),

    SUM(Units * Sub_Units * CASE WHEN Status = 2 THEN 1 ELSE 0 END)

    FROM Item_Inbound

    GROUP BY Item_Code

  • Jezus Paul,

    Right from the first time. And it's so simple.

    Many thanks Paul

     

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

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