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.
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 SELLSfrom CL22where cusip is NOT NULLand CUSIP!=' 'and CUSIP!='0'group by CUSIP, partORDER BY CUSIPSo 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 SELLS000045000 M198614_81402 1 0 0000361AP0 A99128_91451 11 3 000037BAA0 N2502134_914501 1 0 000037BAB8 N2502134_914501 1 0 000037BAC6 N2502134_914501 1 0 000037CNR7 T90624_91451 2 2 000037GAZ4 T90624_91451 1 0 000037GBA8 T90624_91451 1 0 000038AAA1 FR371718_9144 1 1 000038AAA1 N2502134_914501 0 1 0Any help would be great appreciated - new to this. |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
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 |
|
|
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. |
|
|
|
|
|