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
 limiting result set based on conditions

Author  Topic 

dirtyeggs
Starting Member

4 Posts

Posted - 2013-01-30 : 12:22:04
I am stumped on how to finish this query. The query ideally is to pull out specific information on orders but only under certain conditions. Those conditions are - there must be more than one record where the cusip is the same but the participant and the trans_type are different (BUYL is distinct whereas SELLL and SELLS can be considered the same).

Here is the query and a small data sample - (this is all in one table)

select cusip,part
,sum(case Trans_type when 'BUYL' then 1 else 0 end) as BUYL,
sum(case Trans_type when 'SELLL' then 1 else 0 end) as SELL,
sum(case Trans_type when 'SELLS' then 1 else 0 end) as SELLS
from CL22
where cusip is NOT NULL
and CUSIP!=' '
and CUSIP!='0'
group by CUSIP, part
ORDER BY CUSIP

So in the small data set below - I would want nothing to show with the exception of the two records at the bottom. Any help would be appreciated.


cusip part BUYL SELL SELLS
000045000 M198614_81402 1 0 0
000361AP0 A99128_91451 11 3 0
00037BAA0 N2502134_914501 1 0 0
00037BAB8 N2502134_914501 1 0 0
00037BAC6 N2502134_914501 1 0 0
00037CNR7 T90624_91451 2 2 0
00037GAZ4 T90624_91451 1 0 0
00037GBA8 T90624_91451 1 0 0
00038AAA1 FR371718_9144 1 1 0
00038AAA1 N2502134_914501 0 1 0


Any help would be great appreciated - new to this.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-30 : 13:06:26
Any change you can post some sample data (in a consumable format) and expected output?

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

jardrake
Starting Member

3 Posts

Posted - 2013-01-31 : 00:02:13
I think you are looking for a subselect query if I am not mistaken.

I am a writer for the SQL Tutorial and Reference (http://www.afterhoursprogramming.com/tutorial/SQL/Overview/) section of afterhoursprogramming.com
Go to Top of Page

dirtyeggs
Starting Member

4 Posts

Posted - 2013-02-01 : 11:28:50
Thanks for the replies I actually did figure it out.

Two queries and an inner join did the trick.

Much appreciation for the replies.
Go to Top of Page
   

- Advertisement -