兩列值乘以日期範圍的 SQL SUM

Question

我需要回答的問題是「為一位名叫『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');

Answer 1

嘗試這個查詢：

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;