How do I parse a subquery to aggregate a value as part of a query

I have a database with the following information available.
MemID, Origdate, Period
the Period field can have a value of 1,2, or 3
Part of the query is
Select origdate from table where memid = @ID and perid = '3'
or
Problem area follows
memid = @ID and (origdate has both  period = '1' and period = '2')
I am trying to find  records where Memid   has  origdate with period 1 and same origdate with period 2
mbartAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbartAuthor Commented:
Additional information
Have following records
memid = 123 origdate = 2/22/2008 period = '1'
memid =123 origdate = 2/22/2008 period ='2'
memid = 123 origdate = 2/23/2008 period ='1'
memid =123 origdate = 2/24/2008 period ='2'
Want to return origdate 2/22/2008 based on having  both period 1 and period 2
Sure it can be done, just not smart enough to do it.

0
dqmqCommented:
Select T1.* from YourTable T1 where
T1.period = '1' and T1.memid=@ID
and exists
(select * from YourTable T2
 where T1.memid = T2.memid
     and T1.origdate = T2.origdate
     and T2.period = '2'
)
0
Vadim RappCommented:
select t1.origdate
from mytable t1 inner join mytable t2
on t1.memid=t2.memid
and t1.origdate=t2.origdate
and t1.period='1' and t2.period='2'
and t1.memid=@ID
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

jarwCommented:
I'm guessing from your question that you also wanted the Origdate from any record with Period = '3' matching the same MemID. The query below should work as well, although dqmq's query should be easy enough to modify to get Period = '3' as well.

SELECT DISTINCT t1.Origdate
FROM your_table t1
JOIN your_table t2 ON t1.MemID = t2.MemID
WHERE t1.MemID = @ID
AND (
   t1.Period = '3'
   OR (
      t1.Origdate = t2.Origdate
      AND t1.Period = '1' AND t2.Period = '2'
   )
)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ursangelCommented:
Just append the AND condition

AND (PERIOD = '1' AND PERIOD = '2')
0
mbartAuthor Commented:
Exactly what I needed.  You guys are the best!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.