Question

There are 4 tables as shown below: Claims, defendants, Events and statuscodes.

Claims:

claim_id patient_name

=====================

10 'Smith'

20 'Jones'

30 'Brown'

Defendants

claim_id defendant_name

=======================

10 'Johnson'

10 'Meyer'

10 'Dow'

20 'Baker'

20 'Meyer'

30 'Johnson'

Events:

claim_id defendant_name claim_status change_date

==================================================

10 'Johnson' 'AP' '1994-01-01'

10 'Johnson' 'OR' '1994-02-01'

10 'Johnson' 'SF' '1994-03-01'

10 'Johnson' 'CL' '1994-04-01'

10 'Meyer' 'AP' '1994-01-01'

10 'Meyer' 'OR' '1994-02-01'10

10 'Meyer' 'SF' '1994-03-01'

10 'Dow' 'AP' '1994-01-01'

10 'Dow' 'OR' '1994-02-01'

20 'Meyer' 'AP' '1994-01-01'

20 'Meyer' 'OR' '1994-02-01'

20 'Baker' 'AP' '1994-01-01'

30 'Johnson' 'AP' '1994-01-01'

StatusCodes

claim_status claim_status_desc claim_seq

========================================

'AP' 'Awaiting review panel' 1

'OR' 'Panel opinion rendered' 2

'SF' 'Suit filed' 3

'CL' 'Closed' 4

The claim status of a defendant (with regard to a given claim) is his or her latest claim status, which is the claim status with the highest claim sequence number.

The claim status of a claim is the claim status of the defendant having the lowest claim status of all the defendants involved in the claim. This makes the claim status a minimum of the maximums. For this sample data, the answer would be:

claim_id patient_name claim_status

==================================

10 'Smith' 'OR'

20 'Jones' 'AP'

30 'Brown' 'AP'

The problem is to write a sql query to find the claim status of each claim and display it.

Answer 1

Solution

First we need to Create 4 tables one by one

SQL statements for creating this

CREATE TABLE claims (claim_id NUMBER, patient_name VARCHAR2(20));
INSERT INTO claims VALUES (10,'Smith');
INSERT INTO claims VALUES (20,'Jones');
INSERT INTO claims VALUES (30,'Brown');

---

CREATE TABLE defendants (claim_id NUMBER, defendant_name VARCHAR2(20));
INSERT INTO defendants VALUES(10,'Johnson' );
INSERT INTO defendants VALUES(10,'Meyer' );
INSERT INTO defendants VALUES(10,'Dow' );
INSERT INTO defendants VALUES(20,'Baker' );
INSERT INTO defendants VALUES(20,'Meyer' );
INSERT INTO defendants VALUES(30,'Johnson' );

---

CREATE TABLE legalevents (claim_id NUMBER, defendant_name VARCHAR2(20), claim_status VARCHAR2(2), change_date DATE);
INSERT INTO legalevents VALUES (10,'Johnson' ,'AP' , '1994-01-01');
INSERT INTO legalevents VALUES (10,'Johnson' ,'OR' , '1994-02-01');
INSERT INTO legalevents VALUES (10,'Johnson' ,'SF' , '1994-03-01');
INSERT INTO legalevents VALUES (10,'Johnson' ,'CL' , '1994-04-01');
INSERT INTO legalevents VALUES (10,'Meyer' ,'AP' , '1994-01-01');
INSERT INTO legalevents VALUES (10,'Meyer' ,'OR' , '1994-02-01');
INSERT INTO legalevents VALUES (10,'Meyer' ,'SF' , '1994-03-01');
INSERT INTO legalevents VALUES (10,'Dow' ,'AP' , '1994-01-01');
INSERT INTO legalevents VALUES (10,'Dow' ,'OR' , '1994-02-01');
INSERT INTO legalevents VALUES (20,'Meyer' ,'AP' , '1994-01-01');
INSERT INTO legalevents VALUES (20,'Meyer' ,'OR' , '1994-02-01');
INSERT INTO legalevents VALUES (20,'Baker' ,'AP' , '1994-01-01');
INSERT INTO legalevents VALUES (30,'Johnson' ,'AP' , '1994-01-01');

CREATE TABLE claimStatusCodes (claim_status VARCHAR2(2), claim_status_desc VARCHAR2(40), claim_seq NUMBER);
INSERT INTO claimstatuscodes VALUES ('AP','Awaiting review panel' , 1);
INSERT INTO claimstatuscodes VALUES ('OR','Panel opinion rendered', 2);
INSERT INTO claimstatuscodes VALUES ('SF','Suit filed' , 3);
INSERT INTO claimstatuscodes VALUES ('CL','Closed' , 4);

---

Answer to the Problem

Screenshot

SQL query

SELECT a.claim_id,patient_name,claim_status
FROM (SELECT claim_id,MIN(claim_seq) AS min_claim_seq
FROM (SELECT claim_id,defendant_name,MAX(claim_seq) AS claim_seq
FROM legalevents le, claimstatuscodes cs
WHERE le.claim_status= cs.claim_status
GROUP BY claim_id,defendant_name
)
GROUP BY claim_id
) a
,claims cl
,claimstatuscodes cs
WHERE a.claim_id= cl.claim_id
AND a.min_claim_seq = cs.claim_seq;

Result of the Query

Complete screenshot of the SQL query

---

Answered and given complete query also

all the best

please upvote