June 22, 2009 at 6:21 pm
My problem has three tables which have a common ClaimNumber field.
CREATE TABLE InsuranceClaim(ClaimNumber INT)
INSERT INTO ClaimHeader VALUES (1)
INSERT INTO ClaimHeader VALUES (2)
INSERT INTO ClaimHeader VALUES (3)
INSERT INTO ClaimHeader VALUES (4)
INSERT INTO ClaimHeader VALUES (5)
INSERT INTO ClaimHeader VALUES (6)
INSERT INTO ClaimHeader VALUES (7)
CREATE TABLE MedicalProcedure(ClaimNumber INT, ProcedureNumber INT)
INSERT INTO MedicalProcedure VALUES (1,8694)
INSERT INTO MedicalProcedure VALUES (3,8694)
INSERT INTO MedicalProcedure VALUES (3,3333)
INSERT INTO MedicalProcedure VALUES (5,8694)
INSERT INTO MedicalProcedure VALUES (4,5555)
INSERT INTO MedicalProcedure VALUES (6,8694)
CREATE TABLE Diagnosis(ClaimNumber INT, DiagnosisNumber INT)
INSERT INTO Diagnosis VALUES (1,4019)
INSERT INTO Diagnosis VALUES (1,1234)
INSERT INTO Diagnosis VALUES (3,9999)
INSERT INTO Diagnosis VALUES (4,8888)
INSERT INTO Diagnosis VALUES (5,4019)
Can somebody help me come up with the best T-SQL query that finds every Claim Number where Medical Procedure 8694 was performed, without a diagnosis of 4019?
The expected result set would be:
3
6
Thanks for your time.
JT
June 22, 2009 at 8:22 pm
Like this:
Select distinct p.ClaimNumber
From MedicalProcedure p
JOIN Diagnosis d ON d.ClaimNumber = p.ClaimNumber
WHERE p.ProcedureNumber = 8694
AND d.DiagnosisNumber 4019
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 22, 2009 at 8:29 pm
Here is another solution, you'll have to try them all out.
CREATE TABLE dbo.InsuranceClaim(ClaimNumber INT)
INSERT INTO dbo.InsuranceClaim VALUES (1)
INSERT INTO dbo.InsuranceClaim VALUES (2)
INSERT INTO dbo.InsuranceClaim VALUES (3)
INSERT INTO dbo.InsuranceClaim VALUES (4)
INSERT INTO dbo.InsuranceClaim VALUES (5)
INSERT INTO dbo.InsuranceClaim VALUES (6)
INSERT INTO dbo.InsuranceClaim VALUES (7)
CREATE TABLE dbo.MedicalProcedure(ClaimNumber INT, ProcedureNumber INT)
INSERT INTO dbo.MedicalProcedure VALUES (1,8694)
INSERT INTO dbo.MedicalProcedure VALUES (3,8694)
INSERT INTO dbo.MedicalProcedure VALUES (3,3333)
INSERT INTO dbo.MedicalProcedure VALUES (5,8694)
INSERT INTO dbo.MedicalProcedure VALUES (4,5555)
INSERT INTO dbo.MedicalProcedure VALUES (6,8694)
CREATE TABLE dbo.Diagnosis(ClaimNumber INT, DiagnosisNumber INT)
INSERT INTO dbo.Diagnosis VALUES (1,4019)
INSERT INTO dbo.Diagnosis VALUES (1,1234)
INSERT INTO dbo.Diagnosis VALUES (3,9999)
INSERT INTO dbo.Diagnosis VALUES (4,8888)
INSERT INTO dbo.Diagnosis VALUES (5,4019)
select
ic.ClaimNumber
from
dbo.InsuranceClaim ic
where
(exists(select 1 from dbo.MedicalProcedure mp where mp.ClaimNumber = ic.ClaimNumber)) and
(not exists(select 1 from dbo.Diagnosis dia where dia.ClaimNumber = ic.ClaimNumber and dia.DiagnosisNumber = 4019))
drop table dbo.InsuranceClaim;
drop table dbo.MedicalProcedure;
drop table dbo.Diagnosis
June 23, 2009 at 5:34 pm
Thank you RBarry and Lynn for your solutions. I apologize for the mismatch in the table name between DDL and DML for table InsuranceClaim. I was expecting the resultset to be claim 3 and 6. RBarry's solution results in 1 and 3. Lynn's solution results in 3,4, and 6.
Here's why I'm expecting 3 and 6.
Claim 1 has both procedure 8694 and diagnosis 4019 so it should be ruled out.
Claim 2 has no procedure or diagnosis so it should also be ruled out.
Claim 3 has procedure 8694 and no diagnosis 4019 so it should be in.
Claim 4 has no procedure 8694 so it should be ruled out.
Claim 5 has both procedure 8694 and diagnosis 4019 so it should be ruled out.
Claim 6 has procedure 8694 but no diagnosis 4019 so it should be in.
Claim 7 has no procedure or diagnosis so it should be ruled out.
I hope this helps to explain what I'm looking for.
Thanks again.
JT
June 23, 2009 at 6:36 pm
ahmet erispaha (6/23/2009)
Thank you RBarry and Lynn for your solutions. I apologize for the mismatch in the table name between DDL and DML for table InsuranceClaim. I was expecting the resultset to be claim 3 and 6. RBarry's solution results in 1 and 3. Lynn's solution results in 3,4, and 6.Here's why I'm expecting 3 and 6.
Claim 1 has both procedure 8694 and diagnosis 4019 so it should be ruled out.
Claim 2 has no procedure or diagnosis so it should also be ruled out.
Claim 3 has procedure 8694 and no diagnosis 4019 so it should be in.
Claim 4 has no procedure 8694 so it should be ruled out.
Claim 5 has both procedure 8694 and diagnosis 4019 so it should be ruled out.
Claim 6 has procedure 8694 but no diagnosis 4019 so it should be in.
Claim 7 has no procedure or diagnosis so it should be ruled out.
I hope this helps to explain what I'm looking for.
Thanks again.
JT
So what you want us to do is fix our code for you. I have a better idea, see if you take our code and make them work. If you can't, run into problems come back and ask some questions. If you do, come back and show us what changes you made to get them to work.
You will learn more by doing that, then waiting for us to fix things. I know why mine didn't work, I missed one of your criteria so it wouldn't take me long to fix mine at all. I'd like to see if you can fix it first. It's part of learning to troubleshoot problems.
June 23, 2009 at 6:40 pm
And, for the record, I have made a simple correction to my code and have the results you are expecting: 3 and 6.
Show me what you can do with the code, and if you have problems, I'll help you out.
June 23, 2009 at 7:51 pm
Tough love... you can't beat it 🙂
I guess I was dragging my feet a little because I find correlated queries a bit intimidating but you were right, Lynn. It was a simple modification to your solution.
Thanks for the lesson!
--Create InsuranceClaim table
CREATE TABLE dbo.InsuranceClaim(ClaimNumber INT)
INSERT INTO dbo.InsuranceClaim VALUES (1)
INSERT INTO dbo.InsuranceClaim VALUES (2)
INSERT INTO dbo.InsuranceClaim VALUES (3)
INSERT INTO dbo.InsuranceClaim VALUES (4)
INSERT INTO dbo.InsuranceClaim VALUES (5)
INSERT INTO dbo.InsuranceClaim VALUES (6)
INSERT INTO dbo.InsuranceClaim VALUES (7)
--Create MedicalProcedure table
CREATE TABLE dbo.MedicalProcedure(ClaimNumber INT, ProcedureNumber INT)
INSERT INTO dbo.MedicalProcedure VALUES (1,8694)
INSERT INTO dbo.MedicalProcedure VALUES (3,8694)
INSERT INTO dbo.MedicalProcedure VALUES (3,3333)
INSERT INTO dbo.MedicalProcedure VALUES (5,8694)
INSERT INTO dbo.MedicalProcedure VALUES (4,5555)
INSERT INTO dbo.MedicalProcedure VALUES (6,8694)
--Create Diagnosis table
CREATE TABLE dbo.Diagnosis(ClaimNumber INT, DiagnosisNumber INT)
INSERT INTO dbo.Diagnosis VALUES (1,4019)
INSERT INTO dbo.Diagnosis VALUES (1,1234)
INSERT INTO dbo.Diagnosis VALUES (3,9999)
INSERT INTO dbo.Diagnosis VALUES (4,8888)
INSERT INTO dbo.Diagnosis VALUES (5,4019)
--Lynn Pettis
SELECT ic.ClaimNumber
FROM dbo.InsuranceClaim ic
WHERE
(EXISTS
(SELECT 1
FROM dbo.MedicalProcedure mp
WHERE mp.ClaimNumber = ic.ClaimNumber
AND ProcedureNumber = 8694
)
)
AND
(NOT EXISTS
(SELECT 1
FROM dbo.Diagnosis dia
WHERE dia.ClaimNumber = ic.ClaimNumber
AND dia.DiagnosisNumber = 4019
)
)
--Drop tables
DROP TABLE dbo.InsuranceClaim
DROP TABLE dbo.MedicalProcedure
DROP TABLE dbo.Diagnosis
June 23, 2009 at 7:58 pm
Perfect! Just the change I made in my copy here at home. Of course the 4 minutes it took on my part would have saved you quite a bit of time on yours, but I am hoping the experience was worth it.
Thanks for the feedback.
Please remember, we are here to help, but we also want you to walk away with more than just working code but also a little bit more knowledge. One day, you will be on other side of the coin passing on your knowledge.
October 7, 2020 at 5:11 am
SELECT CL.* FROM #InsuranceClaim CL
JOIN #MedicalProcedure PR ON CL.ClaimNumber = PR.ClaimNumber
WHERE PR.ProcedureNumber = 8694 AND NOT EXISTS (SELECT 1 FROM #Diagnosis WHERE CL.ClaimNumber = ClaimNumber AND DiagnosisNumber = 4019)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply