计算两个不同月份之间日期范围内的价格总和

huangapple go评论65阅读模式
英文:

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>



huangapple
  • 本文由 发表于 2023年2月26日 20:53:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/75572123.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定