The question I need to answer is "What is the total cost of Morphine supplied to a patient called 'John Smith'?"

In my database schema I have the following schemas:

Patient (patientNo, patName, patAddr, DOB)
Ward (wardNo, wardName, wardType, noOfBeds)
Contains (patientNo, wardNo, admissionDate)
Drug (drugNo, drugName, costPerUnit)
Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate)

I've created an example schema for the above here: https://www.db-fiddle.com/f/wecC7cjtryKMErqPskNr41/1

How would I structure the query to multiply costPerUnit*unitsPerDay by the amount of days between startDate and finishDate without manually counting the days?

Currently I have:

SELECT SUM r.unitsPerDay*d.costPerUnit BETWEEN '2022-05-25' AND '2022-06-25' AS TotalCost, p.patName, d.drugName
FROM Prescribed r, Drug d, Patient p
WHERE p.patientNo=r.patientNo AND d.drugNo=r.drugNo AND r.drugNo=1001;

from the following:

insert into Drug (drugNo, drugName, costPerUnit) values (1001, 'Morphine', '25.00');
insert into Patient (patientNo, patName, patAddr, DOB) values (101, 'John Smith', '123 Street', DATE '1990-01-01');
insert into Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate) values (101, 1001, 4, DATE '2022-05-25', DATE '2022-06-25');

CodePudding user response：

Try this query:

SELECT SUM (r.unitsPerDay*d.costPerUnit*(finishDate-startDate)) AS TotalCost, p.patName, d.drugName
FROM Prescribed r, Drug d, Patient p
WHERE p.patientNo=r.patientNo AND d.drugNo=r.drugNo AND r.drugNo=1001;

CodePudding user response：

I have updated your sample data to take into account that the same drug can be prescribed to the same patient multiple times.

insert into Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate) values (101, 1001, 4, '2022-05-25', '2022-06-25');
insert into Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate) values (101, 1001, 2, '2022-08-1', '2022-08-10');

The total number of units of Morphine prescribed would be 32*4 10*2 = 148. And at the cost of 25 per unit the answer should be 3700.

select sum(Drug.costPerUnit * Prescribed.unitsPerDay * (Datediff(Prescribed.finishDate,Prescribed.startDate)   1)) as total_cost
from Patient
join Prescribed on Patient.patientNo = Prescribed.patientNo and Patient.patName = 'John Smith'
join Drug on Prescribed.drugNo = Drug.drugNo and Drug.drugName = 'Morphine';

View on DB Fiddle