Question on Finding a Group Without a Marker

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question on Finding a Group Without a Marker

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2009-10-10 : 18:56:09

It's hard to describe the subject of what I'm after, but in the table below, I want to see all the WAREID + PRODID's that don't have at least one row with a type of "S". In the case below, I would want this WAREID + PRODID returned:

111111|BBBBBB

Note that it's essential I only get just one row back, even though there may be hundreds of rows, with none of them having an "S" in their type. I've been able to figure out how to do summing of the quantities (in a separate SQL statement), but am at a loss at how to do this. Thanks in advance for your consideration.

---------------------
tblProductHistory:
(key is an identity column)
WarehouseID
ProductID
Year
Quarter
Type ("S" = Sale, "P" = purchase, "C" = credit)
Qty (sold or purchased or credited)

WAREID|PRODID|YEAR|Q|T|QTY
111111|AAAAAA|2008|2|S|003
111111|AAAAAA|2009|1|S|001
111111|AAAAAA|2009|2|P|007
111111|AAAAAA|2009|1|S|008
111111|AAAAAA|2009|4|S|002
111111|BBBBBB|2009|6|P|005
111111|BBBBBB|2009|7|C|001
111111|CCCCCC|2009|3|S|006
111111|CCCCCC|2009|3|S|004
111111|CCCCCC|2009|3|S|009

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-10 : 20:00:33

So in your example it shouldn't be just
111111|BBBBBB

Show your wanted output please in relation to sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

BobRoberts
Posting Yak Master

109 Posts

Posted - 2009-10-11 : 01:53:13

quote:
Originally posted by webfred

So in your example it shouldn't be just
111111|BBBBBB

Show your wanted output please in relation to sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

If I understand your question correctly, I can respond to it by cutting and pasting a section of my original posting:

In the case below, I would want this WAREID + PRODID returned:

111111|BBBBBB

If I'm misinterpreting your question, please let me know.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-11 : 02:09:51

[code]
select distinct WarehouseID , ProductID
from tblProductHistory h
where not exists
(
select *
from tblProductHistory x
where x.WarehouseID = h.WarehouseID
and x.ProductID = h.ProductID
and x.Type = 'S'
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

BobRoberts
Posting Yak Master

109 Posts

Posted - 2009-10-11 : 20:20:37

Neat. That seems to have done the trick. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources