I have this query that someone wrote in our company with the sql designer. I would like to create this in a Crystal report but do not know how to do the having piece. We have members that have more than one eligibility record, but want to find only those that are effective for a certain month and have had no prior history record. Can someone suggest how to accomplish this? Below is the query.
select
mh.seq_memb_id,
mh.effective_date,
mh.term_date,
mh.ipa_id
from
hsd_member_elig_history mh
where
seq_memb_id in (
select
mh.seq_memb_id
from
hsd_member_elig_history mh
where
seq_memb_id in(select mh.seq_memb_id
from
xrpt_ipa_master_xref imx,
hsd_member_elig_history mh
where
imx.ipa_id = mh.ipa_id and
mh.effective_date >= to_date(‘01-oct-02’) and
mh.term_date is null and imx.ipa_parent_id in (‘MPD’))
group by seq_memb_id
having count(*) = 1)
select
mh.seq_memb_id,
mh.effective_date,
mh.term_date,
mh.ipa_id
from
hsd_member_elig_history mh
where
seq_memb_id in (
select
mh.seq_memb_id
from
hsd_member_elig_history mh
where
seq_memb_id in(select mh.seq_memb_id
from
xrpt_ipa_master_xref imx,
hsd_member_elig_history mh
where
imx.ipa_id = mh.ipa_id and
mh.effective_date >= to_date(‘01-oct-02’) and
mh.term_date is null and imx.ipa_parent_id in (‘MPD’))
group by seq_memb_id
having count(*) = 1)