Post reply

I need to convert column warehouse to Wh1 and Wh2 with the corresponding on hand

  • April 6, 2022 at 2:07 pm

    #4011666

    I need to convert column warehouse to Wh1 and Wh2 with the corresponding on hand

     

    select DATENAME(dw,ompRequestedShipDate) as day
    , omlPartID as Part
    , sum(omlOrderQuantity) as ordered 
    , case when imbWarehouseID ='' then 'WH1' else imbWarehouseID end as warehouse
    , cast(imbQuantityOnHand as INT) as onhand


    from SalesOrders
    left outer join SalesOrderLines on omlSalesOrderID=ompSalesOrderID
    left outer join Parts on impPartID=omlPartID
    left outer join PartRevisions on imrPartID=omlPartID
    left outer join PartBins on imbPartID=omlPartID

    where ompClosed!=-1 and imbWarehouseID!='vw'  and UOMPTRUCKNUMBER!='' 
    and  ompRequestedShipDate >=getdate() and ompRequestedShipDate <=getdate()+6

    group by ompRequestedShipDate,omlPartID, imbWarehouseID,imbQuantityOnHand

    it looks like this nowoutputI need it to look like this

    output2

     

     

  • April 6, 2022 at 8:54 pm

    #4011817

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

    Short version

    Post table create statements, and sample data INSERT statements.

    • This reply was modified 2 years ago by  homebrew01.

  • April 6, 2022 at 9:07 pm

    #4011819

    What you are looking to do here is pivot the data. As noted above, we would prefer to see DDL that helps set up the problem. Use a small set of data that outlines the various cases you have.

    In terms of pivot, this is the type of thing you want to do: https://www.sqlservercentral.com/search/pivot

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

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