Multi Column and Multi Row Filter

Transact-SQL
1

Good Day Everyone -- Need some help on this one I feel as if I am driving in a blinding snowstorm. Multiple hours search and experimenting. Officially stuck.

Just for clarification (SKU = Stock Keeping Unit). the reason we have multiple is because each SKU row represents a warehouse.

Goal: SKU number or items that have the same SKU and a RestockingBasis of 2 across all rows with the same SKU number.

SKU RestockingBasis Warehouse
1 2 1
1 2 2
1 2 3
2 2 4
2 0 1
2 2 2
2 2 3
3 0 1
4 2 1
4 2 2
5 0 1
5 0 3

End Result Desired (SKU Matches and Restocking Basis = 2)

SKU
1
4

Reasoning
< SKU 2 Not a candidate because value 0 RestockingBasis in one of the records
< SKU 3 Not a candidate because value 0 RestockingBasis in one of the records
< SKU 5 Not a candidate because value 0 RestockingBasis in all the records

Thank you so much in advance if you can help determine an appropriate solution.

2 
SELECT 
	SKU
FROM
	YourTable
GROUP BY
	SKU
HAVING
	SUM(CASE WHEN RestockingBasis = 2 THEN 1 ELSE 0 END) = COUNT(*);
1 Like
3

Thank you very much James. This solution worked perfectly!!!! Greatly appreciated.

4

select distinct SKU from stock
where SKU not IN (select SKU from stock where RestockingBasis! = 2)

5

With this same scenario is their a way to determine the SKU number where the Restocking Basis has a conflicts across warehouses. (For example: SKU 2 --- has both 2 and 0 restocking basis.) This will help us fix inventory that has conflicts.

SKU 1 (Same) Not a candidate
SKU 2 (Both 0 and 2 in RestockingBasis) Filter for this
SKU 3 (Same) Not a candidate
SKU 4 (Same) Not a candidate
SKU 5 ( Same) Not a candidate

End Result Desired (SKU has multiple Restocking Basis values)
SKU
1

6 
SELECT 
	SKU
FROM
	YourTable
GROUP BY
	SKU
HAVING
	COUNT(DISTINCT RestockingBasis) > 1;
1 Like
7

Excellent!!!! Worked great.
I ended up entering a where clause after the From statement to filter for only stock that was active.
I sincerely appreciate the help with this one !!!!

8

hi

alternate solution

different way of doing ..another way of thinking .. simpler ..execution plan .. low cost etc etc

if it helps you .. GREAT
:slight_smile:
:slight_smile:

Drop Create Data 
use  tempdb 
go 


drop table data 
go 


create table data 
(
sku  int ,
RestockingBasis   int ,  
Warehouse  int 
)
go 

insert into data select 1, 2, 1
insert into data select 1, 2, 2
insert into data select 1, 2, 3
insert into data select 2, 2, 4
insert into data select 2, 0, 1
insert into data select 2, 2, 2
insert into data select 2, 2, 3
insert into data select 3, 0, 1
insert into data select 4, 2, 1
insert into data select 4, 2, 2
insert into data select 5, 0, 1
insert into data select 5, 0, 3
go 

select * from data
go
SQL 
SELECT DISTINCT sku 
FROM   data 
WHERE  sku NOT IN (SELECT DISTINCT sku 
                   FROM   data 
                   WHERE  restockingbasis <> 2)
Result

image