asked on

Question 2 (SQL)

The database contains a simplified healthcare claim data model consisting of two tables:
1.      MedicalClaimheader: Contains 1 record (claim) per medical encounter. The table contains the following columns;
a.      MemberNumber
b.      ClaimNumber

2.      MedicalClaimDetail: Contains 0 or more records for each procedure made during a medial encounter. The table contains the following columns;
a.      ClaimNumber
b.      Procedure
c.      ProcedureDate
d.      LineNumber
The MedicalClaimDetail table is related to the MedicalClaimheader table by the ClaimNumber field.

I need to write a T-SQL query that finds every MedicalClaimDetail row where a procedure J1234 was performed multiple times on the same day for the same member.

Alfredo Luis Torres Serrano

Select MedicalClaimDetail.ClaimNumber, Count(MedicalClaimDetail.Procedure) From MedicalClaimDetail Left Outer Join MedicalClaimheader On
MedicalClaimDetail.ClaimNumber = MedicalClaimheader.ClaimNumber
Having MedicalClaimDetail.Procedure = 'J1234' And Count(MedicalClaimDetail.Procedure) > 1
Group by MedicalClaimDetail.ClaimNumber

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

and the one posted by Masteraco should be this one I guess (syntax is not correct)

SELECT medicalclaimdetail.claimnumber, 
       COUNT(1) 
FROM   medicalclaimdetail 
       LEFT OUTER JOIN medicalclaimheader 
         ON medicalclaimdetail.claimnumber = medicalclaimheader.claimnumber 
WHERE  medicalclaimdetail.procedure = 'J1234' 
GROUP  BY medicalclaimdetail.claimnumber 
HAVING COUNT(1) > 1

Open in new window

slatefamily

ASKER

well I had went ahead and selected this, but noticed it did not have anything regarding on the same procedure day?

Alfredo Luis Torres Serrano

Then HainKurt should solve the problem.

Good luck