outer join

[Veejc]

I have this
the left table is people, I want to return all the people that are born during a certain date period (where clause)

The other table is a claims table. from that table I need to only determine whether that person from the left table has had a claim that has a certain code.

I want to return everyone, though so I know some of the right sides results will be null and that's okay. In the end I simply want just a "flag" stating that this person has either had or not had this type of claim.

What is the best way to do this?



select aasubno, aapersno, aadob, claim.cbproccode
from dbo.tbl_CurrentMemberDimension as member
left outer join dbo.vw_Professional_Claims as claim
on member.aasubno = claim.casubno and
member.aapersno = claim.capersno

where member.aadob between '01/01/2003' and '12/31/2003'

-- and claim.cbproccode = '83655'
-- i don't think i want this 83655 filter here, but I want to somehow flag whether a person has had this code in a claim
order by aasubno

 

[amrita418]

You code was right on. All you needed was a case stmt.

select 		member.aasubno,
                member.aapersno,
                member.aadob,
                claim.cbproccode,
                case when claim.cbproccode is null then 'N' else 'Y' end
from 		dbo.tbl_CurrentMemberDimension as member
                left outer join dbo.vw_Professional_Claims as claim
                on member.aasubno = claim.casubno
                and member.aapersno = claim.capersno
where 		member.aadob between '01/01/2003' and '12/31/2003'

Regards,
AA

 

[Veejc]

Can you help me understand why I am looking for NULL? or is that where I plug in the number I want to look for - the 83655.

 

[Juice05]

Null would represent there being no claim, so he is putting a "N" for "No".

If you want to have a "Y" for anyone with that code change the case statement to this:

case when claim.cbproccode = '83655' then 'Y' else 'N' end

 

[Veejc]

Thanks for that.

This returns all the claims, though too, I just ran it. I get all the members as many times as there are claims for that member.

What am I doing wrong?

I'm after each member ONE time with a Y or N to the question of do they have a claim with this proccode.

I probably didn't ask the right way, sorry.

 

[Juice05]

Try this

Select member.aasubno,
       member.aapersno,
       member.aadob,
       claim.cbproccode,
       case when Count(claim.cbproccode) = 0 then 'N' else 'Y' end Code
from
dbo.tbl_CurrentMemberDimension as member
left join dbo.vw_Professional_Claims as claim on member.aasubno = claim.casubno And claim.cbproccode = '83655'
Group By member.aasubno,
       member.aapersno,
       member.aadob,
       claim.cbproccode

 

[amrita418]

OR

select         member.aasubno,
               member.aapersno,
               member.aadob,
               claim.cbproccode,
               max(case when claim.cbproccode is null then 'N' else 'Y' end)
from           dbo.tbl_CurrentMemberDimension as member
               left outer join dbo.vw_Professional_Claims as claim
               on member.aasubno = claim.casubno
               and member.aapersno = claim.capersno
where          member.aadob between '01/01/2003' and '12/31/2003' 
group by       member.aasubno,
               member.aapersno,
               member.aadob,
               claim.cbproccode

Regards,
AA