Creating a Different Filter for One Column

Transact-SQL
1

I have this select query on two tables, I pulling the data for everything with a warehouse code = 002, however I have one field QuantityOnPurchaseOrder that I want to pull the data for warehouse code 009, just in that one column all other columns to stay with 002 as the filter. I assume this is easy and I can't seem to see how it is done.

SELECT        TOP (100) PERCENT dbo.IM_ItemWarehouse.ItemCode, dbo.IM_ItemWarehouse.QuantityOnHand, dbo.IM_ItemWarehouse.QuantityOnSalesOrder, dbo.IM_ItemWarehouse.QuantityOnPurchaseOrder, 
                         dbo.IM_ItemWarehouse.ReorderPointQty, dbo.IM_ItemWarehouse.MinimumOrderQty, dbo.IM_ItemWarehouse.LastPhysicalCountDate
FROM            dbo.IM_ItemWarehouse INNER JOIN
                         dbo.CI_Item ON dbo.IM_ItemWarehouse.ItemCode = dbo.CI_Item.ItemCode
WHERE        (dbo.IM_ItemWarehouse.WarehouseCode = '002') AND (dbo.IM_ItemWarehouse.ReorderMethod = 'R') AND (dbo.IM_ItemWarehouse.ItemCode LIKE 'WPF%')
ORDER BY dbo.IM_ItemWarehouse.ItemCode
2

hi

one idea is case when

get both 002 and 009

case when 002 then col1 end , case when 009 col23 end

or else

you can join


select 
     col002 
   , col009 
(select joincol,col002 from table where 002 )  a 
   join 
(select joincol, col009 from table where 009 )  b 
   on a.joincol = b.joincol
3

Using CASE is if you want to output a static variable correct? Or you can assign the output to be a field?

Very new to SQL so your CASE idea example is going over my head.

4

I'll assume the ItemCode in the 009 data needs to match the ItemCode from the 002 data. Please keep in mind that we know nothing about your data, you have to tell us.


SELECT        TOP (100) PERCENT IM.ItemCode, IM.QuantityOnHand, IM.QuantityOnSalesOrder, IM009.QuantityOnPurchaseOrder, 
                         IM.ReorderPointQty, IM.MinimumOrderQty, IM.LastPhysicalCountDate
FROM            dbo.IM_ItemWarehouse IM INNER JOIN
                dbo.IM_ItemWarehouse IM009 ON (IM009.ItemCode = IM.ItemCode) AND (IM009.WarehouseCode = '009') AND (IM009.ReorderMethod = 'R') AND (IM009.ItemCode LIKE 'WPF%') INNER JOIN
                         dbo.CI_Item CI ON IM.ItemCode = CI.ItemCode
WHERE        (IM.WarehouseCode = '002') AND (IM.ReorderMethod = 'R') AND (IM.ItemCode LIKE 'WPF%')
ORDER BY IM.ItemCode
5

Ok, so I received a result with your code but it only produces 6 records and should of been about 100 or so.
In the data below (i removed some filters to show all records possible) I would want to display the WPF01-CC102-FROST record with all values in the row with warehouse code = 002 except the quantity on purchase order, that field I would want to display the 1000 from the row with the warehouse code =009

WPF01-CC102-FROST 0.000000 0.000000 0.000000 0.000000 0.000000 1753-01-01 00:00:00.000 000
WPF01-CC102-FROST 0.000000 100.000000 0.000000 0.000000 0.000000 1753-01-01 00:00:00.000 002
WPF01-CC102-FROST 0.000000 1000.000000 1000.000000 0.000000 0.000000 1753-01-01 00:00:00.000 009

And in this example for the WPF01-LOG104-NAVY-XL record I would want to show the following

WPF01-LOG104-NAVY-S 0.000000 0.000000 0.000000 0.000000 0.000000 1753-01-01 00:00:00.000 000
WPF01-LOG104-NAVY-S 5.000000 0.000000 0.000000 3.000000 2.000000 2023-06-22 00:00:00.000 002
WPF01-LOG104-NAVY-XL 0.000000 0.000000 1.000000 0.000000 0.000000 1753-01-01 00:00:00.000 000
WPF01-LOG104-NAVY-XL 2.000000 2.000000 0.000000 3.000000 2.000000 2023-06-22 00:00:00.000 002
WPF01-LOG104-NAVY-XL 0.000000 6.000000 0.000000 3.000000 2.000000 1753-01-01 00:00:00.000 009

output

|WPF01-LOG104-NAVY-XL|2.000000|2.000000|1.000000|3.000000|2.000000|2023-06-22 00:00:00.000|002|

6

? Maybe you should remove the extra conditions on the 009 rows:
AND (IM009.WarehouseCode = '009') AND (IM009.ReorderMethod = 'R') AND (IM009.ItemCode LIKE 'WPF%')

I don't know for sure. AGAIN, WE HAVE NO ACCESS TO YOUR DATA. And a splat of your data on the screen is absolutely meaningless to us. It would only have value if you posted CREATE TABLE statement(s) and INSERT data statement(s).

7
2 Likes
8

Update, got a solution through excel to get the correct data to display, but also wanted to still figure out a solution in SQL, so got some help from an inside consultant, and they provide this code.

WITH wh2data AS (SELECT dbo.IM_ItemWarehouse.ItemCode, dbo.IM_ItemWarehouse.QuantityOnHand, dbo.IM_ItemWarehouse.QuantityOnSalesOrder, dbo.IM_ItemWarehouse.QuantityOnPurchaseOrder, 
                         dbo.IM_ItemWarehouse.ReorderPointQty, dbo.IM_ItemWarehouse.MinimumOrderQty, dbo.IM_ItemWarehouse.LastPhysicalCountDate
FROM            dbo.IM_ItemWarehouse INNER JOIN
                         dbo.CI_Item ON dbo.IM_ItemWarehouse.ItemCode = dbo.CI_Item.ItemCode
WHERE        (dbo.IM_ItemWarehouse.WarehouseCode = '002') AND (dbo.IM_ItemWarehouse.ReorderMethod = 'R') AND (dbo.IM_ItemWarehouse.ItemCode LIKE 'WPF%')),

wh9data AS (SELECT dbo.IM_ItemWarehouse.ItemCode, dbo.IM_ItemWarehouse.QuantityOnPurchaseOrder
FROM            dbo.IM_ItemWarehouse INNER JOIN
                         dbo.CI_Item ON dbo.IM_ItemWarehouse.ItemCode = dbo.CI_Item.ItemCode
WHERE        (dbo.IM_ItemWarehouse.WarehouseCode = '009') AND (dbo.IM_ItemWarehouse.ReorderMethod = 'R') AND (dbo.IM_ItemWarehouse.ItemCode LIKE 'WPF%'))

SELECT wh2data.ItemCode, wh2data.QuantityOnHand, wh2data.QuantityOnSalesOrder, wh2data.QuantityOnPurchaseOrder,
	wh2data.ReorderPointQty, wh2data.MinimumOrderQty, wh2data.LastPhysicalCountDate, wh9data.QuantityOnPurchaseOrder AS QtyOnPurchaseOrderWh9
FROM wh2data FULL OUTER JOIN wh9data ON wh2data.ItemCode = wh9data.ItemCode;
9

Ah, so sometimes the 002 row is missing, that is why my code didn't work.