英文:
Calculating SUM of price between two dates in different months
问题
所以我正在尝试根据预订情况创建公寓的月度报告。
我有一个名为bookings的表,包含ID、到达日期、离开日期和每晚价格。
ID | 到达日期 | 离开日期 | 每晚价格 |
---|---|---|---|
1 | 2023年1月29日 | 2023年2月2日 | 50 |
一切都很正常,直到日期介于两个月之间。例如:
到达日期= '2023年1月29日',离开日期= '2023年2月2日',价格为50。
因此,在我的月度报告中,我希望将1月份的三个晚上(150)和2月份的一天(50)相加。
那么我该如何计算呢?
这是我如何获得价格总和的方式:
我使用上个月,因为我想要自动创建月度报告。
$date = date('m') - 1; //上个月
$sql = "SELECT SUM(DATEDIFF(departure_date, arrival_date) * price_per_night) sum_price FROM bookings WHERE MONTH(arrival_date) = $date";
当我运行这段代码时,我得到了整个预订的总和,即200(4晚 * 50)。
我想要得到仅针对1月的3晚的总和,即150(3晚 * 50)。
并且当我在下个月3月运行程序时,我希望将剩下的50添加到2月(1晚 * 50)。
英文:
So im trying to create a monthly report for an apartment based on bookings.
I have a table called bookings with ID, arrival_date, departure_date, price_per_night.
ID | arrival_date | departure_date | price_per_night |
---|---|---|---|
1 | 29.1.2023 | 2.2.2023 | 50 |
Everything works fine untill a date is between two months. For example:
arrival_date = '29.1.2023', departure_date = '2.2.2023' and price: 50.
So in my monthly report i would like to add the three nights that were in january to january(150) and the one day in February to add it to February(50).
So how can I calculate this?
This is how i get the SUM of the price:
I use the previous month, because i want to automate the creation of the monthly report.
$date = date('m') - 1; //previous month
$sql = "SELECT SUM(DATEDIFF(departure_date, arrival_date) * price_per_night) sum_price FROM bookings WHERE MONTH(arrival_date) = $date
When I run this I get the SUM of the whole booking so 200 (4 nights * 50).
I would like to get the SUM only for the 3 nights in January so 150 ( 3 nights * 50)
And when I run the program the next month in March I would like to get the remaining 50 from the booking added to February (1 night * 50).
答案1
得分: 0
在MySQL中,晚于到达日期的日期必须先出现,才能得到正的日期差异。
因为你只想要晚上的价格,数学很简单,从日期差异中减去1。
SELECT SUM((DATEDIFF(departure_date, arrival_date) - 1) * `price_per_night`) sum_price
FROM `bookings`
WHERE MONTH(arrival_date) = 1
``` | sum\_price |
|----------:|
| 150 |
[fiddle](https://dbfiddle.uk/aSj8NVRk)
你可以根据需要调整它以适应一晚或多个月。
SELECT MONTH(arrival_date), YEAR(arrival_date), SUM(
CASE WHEN DATEDIFF(departure_date, arrival_date) > 1 THEN
(DATEDIFF(departure_date, arrival_date) - 1)
ELSE 1 END * price_per_night
) sum_price
FROM bookings
GROUP BY MONTH(arrival_date), YEAR(arrival_date)
|--------------------:|-------------------:|----------:|
| 1 | 2023 | 150 |
[fiddle](https://dbfiddle.uk/idHWy9n0)
<details>
<summary>英文:</summary>
In MySQL the later date must be first before the arruvak date, ti get a positive datediff.
as you only wants night, the math is simple subtract 1 from the date differenz
SELECT SUM((DATEDIFF(departure_date,arrival_date) - 1) * price_per_night
) sum_price
FROM bookings
WHERE MONTH(arrival_date) = 1
| sum\_price |
|----------:|
| 150 |
[fiddle](https://dbfiddle.uk/aSj8NVRk)
You can adept it for one night or evern for multiple Month
see
SELECT MONTH(arrival_date), YEAR(arrival_date), SUM(
CASE WHEN DATEDIFF(departure_date,arrival_date) > 1 THEN
(DATEDIFF(departure_date,arrival_date) - 1)
ELSE 1 END * price_per_night
) sum_price
FROM bookings
GROUP BY MONTH(arrival_date), YEAR(arrival_date)
| MONTH(arrival\_date) | YEAR(arrival\_date) | sum\_price |
|--------------------:|-------------------:|----------:|
| 1 | 2023 | 150 |
[fiddle](https://dbfiddle.uk/idHWy9n0)
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论