Home > Software engineering >  List the discharged patients who were earlier admitted but did not undergo any operation
List the discharged patients who were earlier admitted but did not undergo any operation

Time:10-04

This is my code and everything is correct excepted the average temperature since the results are shown that everyone got the same temperature. exp:student a got 4 times observation of temperature whereas student b only got 1 time of observation. so how can I deal with it?

SELECT
    patient_id AS "Patient ID",
    CONCAT(CONCAT(first_name,' '),surname) AS "Patient Name" ,
    admission_date AS "Date Admitted",
    discharge_date AS "Date Discharged",
    ROUND(AVG(observ_value),2) AS "Average Temperature",
    daily_charge AS "Expenses"
FROM person,ward,admission,observation
WHERE person.person_id = admission.patient_id 
AND admission.admission_id NOT IN (SELECT operation.admission_id FROM operation)
AND ward.ward_code = admission.ward_code
AND observ_type = 'Temp' 
AND discharge_date IS NOT NULL 
GROUP BY patient_id, admission_date, discharge_date, daily_charge, first_name, surname
ORDER BY admission_date

CodePudding user response:

This is essentially a complete answer. It shows that writing the SQL more clearly, and with the suggested JOIN / ON form, can more clearly point out when join criteria is missing:

New result with new schema and a little sample data

Old result with the same data and missing join criteria:

enter image description here

Here's the partial old query result (from the question) with the full data from the OP

enter image description here

Here's the new result with full data, with the proposed solution above:

This allows patients which have no temperature observations.

enter image description here

Note: There could be other problems in the logic that the question does not ask about. The missing join criteria of the original SQL certainly does create a mess. This is now cleaned up.

Here's the kind of detail the question should provide, along with some usable test data that produces the result you don't expect:

alter session set NLS_DATE_FORMAT='DD/MM/YYYY';

CREATE TABLE PERSON
(
  Person_id      NUMBER(3) PRIMARY KEY,
  Surname        VARCHAR2(20),
  First_name     VARCHAR2(20),
  Sex            CHAR(1),
  Birth_date     DATE,
  Street         VARCHAR2(40),
  Town           CHAR(20),
  Postcode       NUMBER(4),
  Next_of_kin    NUMBER(3)
);

CREATE TABLE STAFF
(
  Person_id     NUMBER(3) PRIMARY KEY,
  Start_date    DATE,
  Staff_type    VARCHAR2(15),
  Charges       NUMBER(10,2),
  Resign_date   DATE,
  FOREIGN KEY (Person_id) references PERSON(Person_id)
);

CREATE TABLE WARD (
   Ward_code         CHAR(3) PRIMARY KEY,
   Ward_name         VARCHAR2(20),
   Bed_count         NUMBER(4),
   Opened_date       DATE,
   Last_painted_date DATE,
   Daily_charge      NUMBER(10,2)
);

CREATE TABLE OPERATION_TYPE (
   Op_code         CHAR(3) PRIMARY KEY,
   Operation_name  VARCHAR2(50),
   Theatre_fee     NUMBER(10,2),
   Days_in         NUMBER(5)
);

CREATE TABLE ADMISSION (
   Admission_id    NUMBER(3) PRIMARY KEY,
   Patient_id      NUMBER(3),
   Admission_date  DATE NOT NULL,
   Expected_op     CHAR(3),
   Admitted_by     NUMBER(3),
   Ward_code       CHAR(3),
   Discharge_date  DATE,
FOREIGN KEY (Patient_id) references PERSON(Person_id),
FOREIGN KEY (Expected_op) references OPERATION_TYPE(Op_code),
FOREIGN KEY (Admitted_by) references PERSON(Person_id),
FOREIGN KEY (Ward_code) references WARD(Ward_code)
);

CREATE TABLE OPERATION (
   Operation_id     NUMBER(3) PRIMARY KEY,
   Actual_op        CHAR(3),
   Admission_id     NUMBER(3),
   Op_date          DATE,
   Surgeon          NUMBER(3),
   Anaesthetist     NUMBER(3),
FOREIGN KEY (Surgeon) references PERSON(Person_id),
FOREIGN KEY (Anaesthetist) references PERSON(Person_id),
FOREIGN KEY (Actual_op) references OPERATION_TYPE(Op_code),
FOREIGN KEY (Admission_id) references ADMISSION(Admission_id)
);

CREATE TABLE OBSERVATION(
   Admission_id  NUMBER(3),
   Observ_date DATE,
   Observ_time   NUMBER(4),
   Observ_type   CHAR(10),
   Observ_value  NUMBER(4),
   Staff_id      NUMBER(3),
PRIMARY KEY (Admission_id,Observ_date,Observ_time,Observ_type),
FOREIGN KEY (Admission_id) references ADMISSION(Admission_id),
FOREIGN KEY (Staff_id) references STAFF(Person_id)
);


-- With test data

INSERT INTO person (Person_id, First_name, Surname) VALUES (1, 'First1', 'Last1');
INSERT INTO person (Person_id, First_name, Surname) VALUES (2, 'First2', 'Last2');

INSERT INTO ward (Ward_code, Daily_charge)  VALUES (1, 100);
INSERT INTO ward (Ward_code, Daily_charge)  VALUES (2, 120);

INSERT INTO admission (admission_id, patient_id, admission_date, discharge_date, ward_code) VALUES (1, 1, current_date, current_date, 1);
INSERT INTO admission (admission_id, patient_id, admission_date, discharge_date, ward_code) VALUES (2, 2, current_date, current_date, 2);


INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (1, 1000, 'Temp', current_date, 1000);
INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (1, 1100, 'Temp', current_date, 1100);
INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (2, 2000, 'Temp', current_date, 2000);
INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (2, 2100, 'Temp', current_date, 2100);
  • Related