using data from different warehouse in a query
Hi guys, I regularly use the query below to get stock information from SAP. However, i would like the 'on order' column to be from a different warehouse to the other data on the query.
SELECT T0.[ItemCode], T1.[ItemName], T0.[WhsCode], T0.[OnHand], T0.[IsCommited], T0.[OnOrder], ( T0.[OnHand]- T0.[IsCommited]) 'Available to Sell',
' ' ' ',
t0.avgprice, t2.price 'Factory Cost Price', t2.currency 'Curr' ,
(select rate from ortt where DATEDIFF(day, ratedate, GETDATE()) = 0 and currency = t2.currency) 'Todays Rate',
(t2.price / (select rate from ortt where DATEDIFF(day, ratedate, GETDATE()) = 0 and currency = t2.currency)) 'GBP @ Todays Rate'
FROM [dbo].[OITW] T0, OITM T1, itm1 t2
where T0.ItemCode = T1.ItemCode
and t2.itemcode = t1.itemcode
and t2.pricelist = 2
and T0.WhsCode in ('pinnacle') and (t1.usertext not like N'000%' or t1.usertext is null) order by T0.ItemCode
as you can see, the warehouse i am using is Pinnacle, but we use Transit for goods that are on order. Is it possible to isolate t0.OnOrder using brackets?
We need to have a sub query which will be something like
SELECT T0.ItemCode, T1.ItemName, T0.WhsCode, T0.OnHand, T0.IsCommited, T0.OnOrder, (SELECT OnOrder FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode = 'Tansit'),
( T0.OnHand- T0.IsCommited) 'Available to Sell',