Two Queries on Same Table Columns?-CodePudding

I'm trying to build a query that will take specific criteria and split it into new columns on the table. For example, below I have the raw data when I do this query:

select Warehouse
, StockCode
, QtyOnHand
, QtyAllocated
, SalesQty1
, SalesQty2
, SalesQty3
from InvWarehouse

Warehouse	StockCode	QtyOnHand
OF	ACN2-1015-2.3	36900.000000
01	ACN2-1015-2.3	22475.000000
OF	ACN2-8125-1.9	108000.000000
01	ACN2-8125-1.9	45600.000000
OF	CA-2520S-151ZY	74632.000000

Some of these parts are in both OF and 01 Warehouses and what I want to do is compare the OF QtyOnHands to the 01 QtyOnHands.

I built this query, but the qtyonhands seem to be wrong so I must be doing something incorrectly. How do I correct this?

select
          WOF.Warehouse
        , WOF.StockCode
        , WOF.QtyOnHand
        , W01.QtyOnHand
        , W01.QtyAllocated
        , W01.[SalesQty1]
        , W01.[SalesQty2]
        , W01.[SalesQty3]
    from InvWarehouse W01
    left join InvWarehouse WOF on WOF.Warehouse = W01.Warehouse
    where W01.QtyOnHand > '0'
    and WOF.Warehouse = 'OF'

As said before, I'm trying to split the data to get two columns for Quantity On Hand. One for OF and one for 01. But I ONLY want to show OF in the warehouse column. So something like the below:

Warehouse	StockCode	W01.QtyOnHand	WOF.QtyOnHand	W01.QtyAllocated	WOF.SalesQty1	WOF.SalesQty2	WOF.SalesQty3
OF	ACN2-1015-2.3	36900.000000	36900.000000	0.000000	0.000000	0.000000	0.000000
OF	ACN2-8125-1.9	108000.000000	36900.000000	0.000000	0.000000	0.000000	0.000000

Let me know if further clarification is needed, but bottom line is I want to pull the data from Warehouse = 01 and also Warehouse = OF and be able to add the QtyOnHand 01 onto my table that has QtyOnHand OF. So basically two queries where one shows Warehouse 01 and the other shows Warehouse OF, combined into one query that I can grab specific info from.

CodePudding user response：

This will not match your expected output exactly. As the comments suggest, I don't think you've described your problem clearly (I don't understand why you would have W01.QtyOnHand as 36900).

However, I suspect you are looking for something like:

select
      WOF.Warehouse
    , W01.StockCode
    , WOF.QtyOnHand
    , W01.QtyOnHand
    , W01.QtyAllocated
    , W01.[SalesQty1]
    , W01.[SalesQty2]
    , W01.[SalesQty3]
from InvWarehouse W01
left join InvWarehouse WOF on WOF.StockCode = W01.StockCode
where W01.QtyOnHand > 0
and WOF.Warehouse = 'OF'
and W01.Warehouse = '01'

Note the join here is ON WOF.StockCode = W01.StockCode (not ON WOF.Warehouse = W01.Warehouse) and I've added a new condition and W01.Warehouse = '01'. All together, this will give you two rows as you expect and the WOF.* columns in your SELECT will refer to the warehouse='OF' row for each StockCode and the W01.* columns in your SELECT will refer to the warehouse='01' row for each StockCode.