April 20, 2006 at 11:22 am
Hello,
I'm a little rusty on sql querries, and need to check myself on the following query;
SELECT
a1.account,
a1.disease_prog,
a1.confirm_flag,
a1.consent_flag,
a1.opt_out_reason_code
FROM ah_member_flags a1
WITH (nolock)
inner join ah_member_flags a2
on a1.account = a2.account
and a1.company = a2.company
and a1.disease_prog = a2.disease_prog
WHERE
a1.confirm_flag = 'y'
and
a1.consent_flag = 'y'
and
(
(
a1.disease_prog = 'HFCCIP'
and
a1.opt_out_reason_code = NULL
)
and
(
a1.disease_prog = 'IMPACT'
and
a1.opt_out_reason_code = 'd'
)
)
So a sample result set might look like this;
account disease_prog confirm_flag consent_flag opt_out_reason_code
2197 HFCCIP Y Y NULL
2197 IMPACT Y Y D
I am not certain that the querry I have created is returning the correct results. Any ideas?
Thank you for your help!
CSDunn
April 20, 2006 at 11:54 am
SELECT
a1.account,
a1.disease_prog,
a1.confirm_flag,
a1.consent_flag,
a1.opt_out_reason_code
FROM ah_member_flags a1
WITH (nolock)
inner join ah_member_flags a2
on a1.account = a2.account
and a1.company = a2.company
and a1.disease_prog = a2.disease_prog
WHERE
a1.confirm_flag = 'y'
and
a1.consent_flag = 'y'
and
(
(
a1.disease_prog = 'HFCCIP'
and
a1.opt_out_reason_code = NULL
)
OR
(
a1.disease_prog = 'IMPACT'
and
a1.opt_out_reason_code = 'd'
)
)
* Noel
April 20, 2006 at 12:44 pm
Hey, I was close! Thanks!
CSDunn
April 20, 2006 at 10:25 pm
SELECT
a1.account,
a1.disease_prog,
a1.confirm_flag,
a1.consent_flag,
a1.opt_out_reason_code
FROM ah_member_flags a1
WITH (nolock)
inner join ah_member_flags a2
on a1.account = a2.account
and a1.company = a2.company
and a1.disease_prog = a2.disease_prog
WHERE
a1.confirm_flag = 'y'
and
a1.consent_flag = 'y'
and
(
(
a1.disease_prog = 'HFCCIP'
and
a1.opt_out_reason_code IS NULL
)
OR
(
a1.disease_prog = 'IMPACT'
and
a1.opt_out_reason_code = 'd'
)
)
Andy
April 25, 2006 at 9:16 pm
try this ....
SELECT
a1.account,
a1.disease_prog,
a1.confirm_flag,
a1.consent_flag,
a1.opt_out_reason_code
FROM ah_member_flags a1 WITH (nolock)
inner join ah_member_flags a2 on a1.account = a2.account
and a1.company = a2.company
and a1.disease_prog = a2.disease_prog
WHERE a1.confirm_flag = 'y'
and a1.consent_flag = 'y'
and a1.disease_prog IN ('HFCCIP', 'IMPACT')
and (a1.opt_out_reason_code IS NULL OR a1.opt_out_reason_code = 'd')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply