SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 limiting result set based on conditions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dirtyeggs
Starting Member

USA
4 Posts

Posted - 01/30/2013 :  12:22:04  Show Profile  Reply with Quote
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.

Edited by - dirtyeggs on 01/30/2013 12:30:07

Lamprey
Flowing Fount of Yak Knowledge

4364 Posts

Posted - 01/30/2013 :  13:06:26  Show Profile  Reply with Quote
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 - 01/31/2013 :  00:02:13  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 02/01/2013 :  11:28:50  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000