SQL Query

[JSHoltsIT]

I have a database with products and location codes.
fields: itemnmbr,locncode

Every item should have a locncode M. We want to find out which items do not have M but have any other locncode. We also need to ignore items that have a blank locncode field.

The database has a record for each itemnmbr & locncode combination. There are multiple records for each item number.

Our task is to identify which items do not have locncode M while they may have other location codes.

TIA

Jason

 

[RyanEK]

Hi,

Showing example data and desired output would help, but I think this is what you want?

select itemnmbr
from [Table]
where code <> 'M' and code <> ''
group by itemnmbr
having count(*) > 1

It will only display item numbers that:
* does not contain the code 'M'
* does not have an empty code
* has other codes

Ryan

 

[JSHoltsIT]

starting with 26,000+ records, your query brings me down to 62.

How can I also display the locncode field?

BTW, thanks for the code.

Jason

 

[RyanEK]

select * 
from [Table] t1
where exists(
  select itemnmbr
  from [Table] t2
  where code <> 'M' and code <> '' 
  and t1.itemnmbr = t2.itemnmbr
  group by itemnmbr
  having count(*) > 1
)

 

[JSHoltsIT]

select itemnmbr,locncode
from mail..iv00102 t1
where exists(
select itemnmbr
from mail..iv00102 t2
where locncode <> 'M' and locncode <> ''
and t1.itemnmbr = t2.itemnmbr
group by itemnmbr
having count(*) > 1
)
order by t1.itemnmbr

returns 248 rows, below is a sample:

CFFPLN32
CFFPLN32 AUTO
CFFPLN32 CASA
CFFPLN32 M
CFFPXN32
CFFPXN32 AUTO
CFFPXN32 CASA
CFFPXN32 M
CFFRDN32
CFFRDN32 AUTO
CFFRDN32 CASA
CFFRDN32 M


These three itemnmbr's have an M. Something is off.

Your thoughts?

Jason

 

[gmmastros]

Try this....

Select   ItemNmbr
From     mail..iv00102
Where    locncode <> ''
Group By ItemNmbr
Having   Sum(Case When locncode = 'M' Then 1 Else 0 End) = 0
         And Sum(Case When locncode <> 'M' Then 1 Else 0 End) > 0

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JSHoltsIT]

Ah, I think this is it.

Thanks guys!

Jason