I almost never ask a question, so please be patient with me.

Example Orders table:

Order_ID	Ord_Partno	Ord_UnitPrice	Ord_Qty
1658712	PN001	$1.20	9
1658712	PN002	$2.40	4
1658712	PN003	$1.40	21
1658712	PN004	$1.10	16

Example Inventory table:

Inv_Partno	Inv_Warehouse	Stock
PN001	Atlanta	14
PN001	AtlantaHold	0
PN001	Chicago	7
PN001	ChicagoHold	0
PN002	Atlanta	5
PN002	AtlantaHold	18
PN002	Chicago	0
PN002	ChicagoHold	0
PN003	Atlanta	2
PN003	AtlantaHold	43
PN003	Chicago	19
PN003	ChicagoHold	0

In this example, each part in Inventory has 4 warehouse entries. The actual stock available, and the amount in Holding for other orders. If either warehouse (Atlanta or Chicago, NOT Hold) has more stock than Ord_Qty (but not combined), I would like to get following result given an Order_ID:

Ord_Partno	Ord_UnitPrice	Ord_Qty	Atlanta	Chicago	InStock
PN001	$1.20	9	14	7	Yes
PN002	$2.40	4	5	0	Yes
PN003	$1.40	21	2	19	No
PN004	$1.10	16	8	9	No

As a beginner I have not been able to replicate this result. I have tried using DISTINCT, LEFT/INNER JOIN, CASE EXISTS ect. Could anyone help me out with this?

Example query:

SELECT DISTINCT
    Ord_Partno, Ord_UnitPrice, Ord_Qty, 
    () AS Atlanta, () AS Chicago, () AS InStock 
FROM 
    Orders 
INNER JOIN 
    Inventory ON Ord_Partno = Inv_Partno
WHERE
    Order_ID = 1658712 
    AND (Inv_Warehouse = "Atlanta" OR Inv_Warehouse = "Chicago")

CodePudding user response：

SELECT 
    Ord_Partno, Ord_UnitPrice, Ord_Qty, 
    AtlantaInv.Stock AS Atlanta,
    ChicagoInv.Stock AS Chicago,
    case when (AtlantaInv.Stock >= Ord_Qty OR ChicagoInv.Stock >= Ord_Qty) then 'Yes' else 'No' end as InStock
FROM 
    Orders 
INNER JOIN 
    Inventory AS AtlantaInv ON Ord_Partno = AtlantaInv.Inv_Partno and AtlantaInv.Inv_Warehouse = "Atlanta"
INNER JOIN 
    Inventory AS ChicagoInv ON Ord_Partno = ChicagoInv.Inv_Partno and ChicagoInv.Inv_Warehouse = "Chicago"

CodePudding user response：

The hold part is what is going to get more annoying if you need to build this out longer, but this is a strong case for sum and case built together.

select ord_partno, ord_unitPrice, Ord_qty,
sum(case when inv_warehouse = 'atlanta' then stock else 0 end) as [Atlanta],
sum(case when inv_warehouse = 'Chicago' then stock else 0 end) as [Chicago],
case when (sum(case when inv_warehouse not like '%hold' then stock else 0 end)) - (sum(case when inv_warehouse like '%hold' then stock else 0 end)) > 0 then 'Yes' else 'No' end as InStock
from
order
inner join inventory on ord_partno = invpartno
where order_id = 1658712
group by ord_partno, ord_unitPrice, Ord_qty

CodePudding user response：

Schema and insert statements:

 create table orders (Order_ID int, Ord_Partno varchar(50), Ord_UnitPrice float, Ord_Qty int);
 insert into orders values(1658712, 'PN001',    1.20,   9);
 insert into orders values(1658712, 'PN002',    2.40,   4);
 insert into orders values(1658712, 'PN003',    1.40,   21);
 insert into orders values(1658712, 'PN004',    1.10,   16);
 
 create table Inventory(Inv_Partno varchar(50), Inv_Warehouse varchar(100), Stock int);
 insert into Inventory values('PN001','Atlanta',    14);
 insert into Inventory values('PN001','AtlantaHold',    0);
 insert into Inventory values('PN001','Chicago',    7);
 insert into Inventory values('PN001','ChicagoHold',    0);
 insert into Inventory values('PN002','Atlanta',    5);
 insert into Inventory values('PN002','AtlantaHold',    18);
 insert into Inventory values('PN002','Chicago',    0);
 insert into Inventory values('PN002','ChicagoHold',    0);
 insert into Inventory values('PN003','Atlanta',    2);
 insert into Inventory values('PN003','AtlantaHold',    43);
 insert into Inventory values('PN003','Chicago',    19);
 insert into Inventory values('PN003','ChicagoHold',    0);

Query:

 select Ord_Partno, Ord_UnitPrice,Ord_Qty,sum(case when Inv_Warehouse ='Atlanta' then Stock else 0 end)'Atlanta',
 sum(case when Inv_Warehouse ='Chicago' then Stock else 0 end)'Chicago',
 (case when Ord_Qty>sum(case when Inv_Warehouse ='Atlanta' then Stock else 0 end) and Ord_Qty>sum(case when Inv_Warehouse ='Chicago' then Stock else 0 end) then 'No' else 'Yes' end)InStock
 from
 orders ord
 left join Inventory inv on ord.Ord_Partno=inv.Inv_Partno
 group by Ord_Partno, Ord_UnitPrice, Ord_Qty

Output:

Ord_Partno	Ord_UnitPrice	Ord_Qty	Atlanta	Chicago	InStock
PN001	1.2	9	14	7	Yes
PN002	2.4	4	5	0	Yes
PN003	1.4	21	2	19	No
PN004	1.1	16	0	0	No

db<>fiddle here