Challenging query for medical claims analysis

  • ahmet erispaha

    Right there with Babe

    Points: 775

    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

  • RBarryYoung

    SSC Guru

    Points: 143327

    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]

  • Lynn Pettis

    SSC Guru

    Points: 442361

    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

  • ahmet erispaha

    Right there with Babe

    Points: 775

    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

  • Lynn Pettis

    SSC Guru

    Points: 442361

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442361

    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.

  • ahmet erispaha

    Right there with Babe

    Points: 775

    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

  • Lynn Pettis

    SSC Guru

    Points: 442361

    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.

  • arebA

    Newbie

    Points: 1

    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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply