I am trying to use SKU to filter down to a result that say SKU 2 and 5 are inactive across warehouses. I have database with a tremendous amount of records and my ultimate goal is is to determine the SKU (Stock Keeping Units) items that are not active across any warehouse. All Locations have to be Active(No) to be included in the result set. (Active is a Binary field) I have tried a bunch of ideas buy am coming up short on this one. Thank you for some guidance on this one.
SKU, WAREHOUSE, Active
1 New York 1
1 Chicago 1
1 Boston 0
use tempdb
go
drop table data
go
create table data
(
SKU int ,
WAREHOUSE varchar(100),
Active int
)
insert into data select 1 ,'New York ',1
insert into data select 1 ,'Chicago ',1
insert into data select 1 ,'Boston ',0
insert into data select 2 ,'New York ',0
insert into data select 2 ,'Chicago ',0
insert into data select 2 ,'Boston ',0
insert into data select 3 ,'New York ',0
insert into data select 3 ,'Chicago ',1
insert into data select 3 ,'Boston ',0
insert into data select 4 ,'New York ',1
insert into data select 4 ,'Chicago ',1
insert into data select 4 ,'Boston ',1
insert into data select 5 ,'New York ',0
insert into data select 5 ,'Chicago ',0
insert into data select 5 ,'Boston ',0
go
SQL ...
;WITH cte
AS (SELECT sku,
Max(active) AS maxc
FROM data
GROUP BY sku)
SELECT *
FROM cte
WHERE maxc = 0
go
Thank you very much for taking the time to thoroughly answer my SQL question. Your SQL skills are exceptional. Again thank you very much - greatly appreciated !!! . I assume others down the road will also benefit from your answers.
Thank you very much for taking the time to thoroughly answer my SQL question. Your SQL skills are exceptional. Again thank you very much - greatly appreciated !!! . I assume others down the road will also benefit from your answers.