SQL compare data from two tables to find where data has been changed

Hello experts,

I have two tables that I am comparing, charges and patient_procedure.  The same 3 pieces of data are supposed to go into each one, a person_id, charge_code, and enc_id.

I am trying to find instances where the charge_code was changed by someone, so I currently have a join statement that returns:

select distinct pp.charge_code, c.charge_code, c.person_id, pp.enc_id
from patient_procedure pp
join charges c
on pp.enc_id = c.source_id
and pp.person_id = c.person_id
and pp.practice_id = c.practice_id
where c.source_type = 'V'
order by pp.enc_id

which gets me:

charge_code      charge_code      person_id      enc_id
24075      70120      718324C8-4809-468B-936E-3994EAB60535      0A0E14C1-2F34-471C-996D-01DA068210EC
24075      99214      718324C8-4809-468B-936E-3994EAB60535      0A0E14C1-2F34-471C-996D-01DA068210EC
70120      24075      718324C8-4809-468B-936E-3994EAB60535      0A0E14C1-2F34-471C-996D-01DA068210EC
70120      99215      718324C8-4809-468B-936E-3994EAB60535      0A0E14C1-2F34-471C-996D-01DA068210EC
99215      24075      718324C8-4809-468B-936E-3994EAB60535      0A0E14C1-2F34-471C-996D-01DA068210EC
99215      70120      718324C8-4809-468B-936E-3994EAB60535      0A0E14C1-2F34-471C-996D-01DA068210EC

I want to end up with a report that only shows me charge_code records that are in the charges table but not in the patient_procedure table, and based on the above, that would be due to the 99214 value for that enc_id:

charge_code      charge_code      person_id      enc_id
24075      99214      718324C8-4809-468B-936E-3994EAB60535      0A0E14C1-2F34-471C-996D-01DA068210EC

So something like:

select distinct pp.service_item_id, c.cpt4_code_id, c.person_id, pp.enc_id
from patient_procedure pp
join charges c
on pp.enc_id = c.source_id
and pp.person_id = c.person_id
and pp.practice_id = c.practice_id
where c.source_type = 'V'
and pp.service_item_id <>  c.cpt4_code_id
order by pp.enc_id


Thoughts?

Thanks!
robthomas09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
I think you're over-thinking it. If you just want "is in A but not in B", a simple left join should work:
select c.charge_code
from charges c
left join patient_procedure pp
on c.charge_code = pp.charge_code
where pp.charge_code is null

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
select charge_code, , person_id, source_id as enc_id from charges
except
select charge_code, , person_id, enc_id from patient_procedure
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
robthomas09, do you still need help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.