• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

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
0
mbart
Asked:
mbart
1 Solution
 
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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
 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now