Skip to Content
0
Dec 21, 2022 at 01:23 AM

Filter column based on query filter

81 Views Last edit Dec 21, 2022 at 01:32 AM 2 rev

Hi Folks,

I am working on a query that provides me quantities in two warehouses.

  1. SELECT
  2. T0.[ItemCode],
  3. T0.[ItemName], T0.[IntrSerial],
  4. ISNULL((SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '177'),0) AS '177',
  5. ISNULL((SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '188'),0) AS '188'
  6. FROM
  7. OSRI T0 INNER JOIN OWHS T1 ON T0.WhsCode = T1.WhsCode
  8. WHERE
  9. T0.[Status] <> 1 and (T1.[WhsCode] = '177' or T1.[WhsCode] = '188')
  10. Group by
  11. T0.[ItemCode], T0.[ItemName], T0.[Quantity], T1.[WhsCode], T0.[IntrSerial]

and it pulls out the info like this.

image.png

what I am trying to achieve here is, if I input WHCODE of my choice it shows the data of only that WH instead of both.

it is to avoid the duplication of data in both WHs. for example, if i have quantity in both WHs, the above query will show me data of (2+3) = 5 times and I don't know that which serial # belongs to which WH.

Any help that leads me to solve this would be appreciated.

Attachments

image.png (6.8 kB)