IT Employment

Ask something
Question Locked

Oracle join table and calculation problem

0 helpful
2 replies

by alex789 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi all and thanks in advance,

I need to output the patient's name, patient's address, item_code, description, and total cost for a particular month in the folloing tables.

Here are the tables

CREATE TABLE patient(
pati_Ref_no NUMBER(6) NOT NULL,
name VARCHAR2(8),
dob DATE,
address VARCHAR2(15),
PRIMARY KEY(pati_Ref_no));

CREATE TABLE items(
item_code NUMBER(4) NOT NULL,
description VARCHAR2(20),
cost NUMBER(6,2),
PRIMARY KEY(item_code));

CREATE TABLE charge(
pati_Ref_no NUMBER(6) NOT NULL,
itemcode NUMBER(4) NOT NULL,
chargedate DATE,
PRIMARY KEY(pati_ref_no, itemcode, chargedate),
FOREIGN KEY(pati_ref_no)REFERENCES patient,
FOREIGN KEY(item_code)REFERENCES items);

Thank you for your reply.

2 replies

by tony hopkinson

Select patient.name,patient.address,items.itemcode, items.description, Sum(items.cost) as total
From Patients,items,charge
Where patients.pati_ref_no = ??
and charge.pati_ref_no = patient.pati_ref_no
and charge.itemcode = items.itemcode
and charge.chargedate between ??? and ???
Group by
patient.name,patient.address,items.itemcode, items.description

is a first go.

There's got's to be a better way, but PL/SQL isn't my strong point.

If you are doing a lot of this I'd look at defaulting cost in it's' charge record

The other problem with where you have it is a price change would mean you have to invent a new item record, otherwise any report 'ran again' would come out with a different number.
Annoys the hell out of customers and bean counters that.

This discussion is closed to new replies.