SQL的两列值的值乘以日期范围

发布于 2025-02-02 14:06:45 字数 1216 浏览 4 评论 0原文

我需要回答的问题是“向名为“约翰·史密斯”的患者提供的吗啡的总成本是多少？”

在我的数据库架构中，我有以下模式：

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/wecc7cjtrykmerqpskmerqpskpsknr41/1

我如何构建查询以乘以costperunit*单位的乘以启动日期和终结者之间的天数，而无需手动计算几天？

目前我有：

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

那请放手 2025-02-09 14:06:45

尝试此查询：

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;

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;

回复收藏 0 原文

黑凤梨 2025-02-09 14:06:45

我已经更新了您的样本数据，以考虑到可以多次向同一患者开出相同的药物。

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

规定的吗啡的单位总数为32*4 + 10*2 = 148。为25的费用应为

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

3700
。

并且按单位

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