SQL SUM of two column values ​​multiplied by date range
P粉129168206
P粉129168206 2024-03-30 22:37:08
0
1
486

The question I need answered is "What is the total cost of providing morphine to a patient named 'John Smith'?"

In my database schema I have the following schema:

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 created a sample schema for the above here: https://www.db-fiddle.com/f/wecC7cjtryKMErqPskNr41/1

How do I construct a query to multiply costPerUnit*unitsPerDay by the number of days between startDate and finishDate without having to manually calculate the number of 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:

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');

P粉129168206
P粉129168206

reply all(1)
P粉517814372

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;
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template