我需要回答的問題是「為一位名叫『John Smith』的病人提供嗎啡的總費用是多少？」

在我的資料庫架構中，我有以下架構：

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

我在這裡為上述內容建立了一個範例架構：https://www.db-fiddle.com/f/wecC7cjtryKMErqPskNr41/1

如何建構查詢以 costPerUnit*unitsPerDay 乘以 startDate 和 finishDate 之間的天數，而無需手動計算天數？

目前我有：

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;

來自以下內容：

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