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