slatefamily
asked on
SQL Question 1 (TSQL)
The database contains a simplified healthcare claim data model consisting of three tables:
1. MedicalClaimheader: Contains 1 record (claim) per medical encounter
2. MedicalProcedure: Contains 0 or more records for each procedure performed during a medical encounter
3. MedicalDiagnosis: Contains 0 or more records for each diagnosis made during a medial encounter.
The MedicalClaimheader table is related to the MedicalProcedure and MedicalDiagnosis tables by the ClaimNumber field.
I need a T-SQL query that finds every claim where a procedure 8694 was performed, without a diagnosis of 4019.
1. MedicalClaimheader: Contains 1 record (claim) per medical encounter
2. MedicalProcedure: Contains 0 or more records for each procedure performed during a medical encounter
3. MedicalDiagnosis: Contains 0 or more records for each diagnosis made during a medial encounter.
The MedicalClaimheader table is related to the MedicalProcedure and MedicalDiagnosis tables by the ClaimNumber field.
I need a T-SQL query that finds every claim where a procedure 8694 was performed, without a diagnosis of 4019.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Select * from MedicalClaimheader MCH
where exists(Select 1 from MedicalProcedure MCP where MCP.ClaimNumber = MCH.ClaimNumber and MCP.procedure = 8694 )
and not exists(Select 1 from MedicalDiagnosis MD where MD.ClaimNumber = MCH.ClaimNumber and MD.diagnosis = 4019)