mysql - 如何计算MySQL中的预订总天数

Question

我有一张表，其中包含每条记录的商店车辆预订我有 dateFrom“预订开始日期”和 dateTo“预订结束日期”

我正在尝试编写一个查询来计算每辆车被预订的总天数以及每辆车产生的总收入。

业务规则如下

1. 如果 dateFrom 和 dateTo 在同一天，则认为是 1 天
2. 如果预订从 2013-05-25 到 2013-06-06 那么 7 天到五月，5 天到五月六月这里是这个逻辑的分解

2013-05-25 - 2013-05-26 (May)
2013-05-26 - 2013-05-27 (May)
2013-05-27 - 2013-05-28 (May)
2013-05-28 - 2013-05-29 (May)
2013-05-29 - 2013-05-30 (May)
2013-05-30 - 2013-05-31 (May)
2013-05-31 - 2013-06-01 (**May**)
2013-06-01 - 2013-06-02 (June)
2013-06-02 - 2013-06-02 (June)
2013-06-03 - 2013-06-02 (June)
2013-06-04 - 2013-06-02 (June)
2013-06-05 - 2013-06-02 (June)

这是一个关于计算应该如何工作的例子。

对于收入，我假设通过将总收入除以总租赁天数，然后将日平均值乘以符合该范围的总天数来计算平均日租金

这是我当前的查询，但它没有正确计算今天。所以在上面的例子中，如果我们假设整个预订的总收入是 1500 美元那么平均日租金为 $1500/12 = $125

因为我们计算的范围是 "2013-06-01 00:00:00" 到 "2013-06-16 23:59:59"，此车辆应显示总天数为 5，总收入为 625 美元。此外，如果范围是 2013-05-01 00:00:00 到 2013-05-31 23:59:59 那么同一辆车总共有 7天数和总收入 875 美元

以下是我当前尝试计算差异的查询。

SELECT rs.vehicle_id,
    ROUND(SUM(


        CASE
        WHEN (rs.dateFrom BETWEEN "2013-06-01 00:00:00" AND "2013-06-16 23:59:59")
        AND (rs.dateTo BETWEEN "2013-06-01 00:00:00"  AND "2013-06-16 23:59:59")
        THEN (rs.totalRent + rs.totalTax)

        WHEN rs.dateTo BETWEEN "2013-06-01 00:00:00"  AND "2013-06-16 23:59:59"
        AND rs.dateFrom < "2013-06-01 00:00:00"
        THEN ( ( (rs.totalRent + rs.totalTax) / CASE WHEN DATEDIFF( rs.dateTo,rs.dateFrom) = 0 THEN 1 ELSE DATEDIFF( rs.dateTo,rs.dateFrom) END) * (DATEDIFF(rs.dateTo, "2013-06-01 00:00:00")) )

        WHEN rs.dateFrom BETWEEN "2013-06-01 00:00:00" AND "2013-06-16 23:59:59"
        AND rs.dateTo > "2013-06-16 23:59:59"
        THEN ( ( (rs.totalRent + rs.totalTax) / CASE WHEN DATEDIFF( rs.dateTo,rs.dateFrom) = 0 THEN 1 ELSE DATEDIFF( rs.dateTo,rs.dateFrom) END) * (DATEDIFF( "2013-06-16 23:59:59",rs.dateFrom)+1) )

        WHEN rs.dateFrom < "2013-06-01 00:00:00" AND rs.dateTo > "2013-06-16 23:59:59"
        THEN ( ( (rs.totalRent + rs.totalTax) / CASE WHEN DATEDIFF( rs.dateTo,rs.dateFrom) = 0 THEN 1 ELSE DATEDIFF( rs.dateTo,rs.dateFrom) END) * (DATEDIFF( "2013-06-16 23:59:59", "2013-06-01 00:00:00") +1) )

        ELSE 0 END 

 )) AS income,


SUM(

        CASE
        WHEN (rs.dateFrom BETWEEN "2013-06-01 00:00:00" AND "2013-06-16 23:59:59")
        AND (rs.dateTo BETWEEN "2013-06-01 00:00:00"  AND "2013-06-16 23:59:59")
        THEN CASE WHEN DATEDIFF( rs.dateTo,rs.dateFrom) = 0 THEN 1 ELSE DATEDIFF( rs.dateTo,rs.dateFrom) END

        WHEN rs.dateTo BETWEEN "2013-06-01 00:00:00"  AND "2013-06-16 23:59:59"
        AND rs.dateFrom < "2013-06-01 00:00:00"
        THEN CASE WHEN DATEDIFF(rs.dateTo, "2013-06-01 00:00:00") = 0 THEN 1 ELSE (DATEDIFF(rs.dateTo, "2013-06-01 00:00:00")) END

        WHEN rs.dateFrom BETWEEN "2013-06-01 00:00:00" AND "2013-06-16 23:59:59"
        AND rs.dateTo > "2013-06-16 23:59:59"
        THEN CASE WHEN DATEDIFF( "2013-06-16 23:59:59",rs.dateFrom) = 0 THEN 1 ELSE (DATEDIFF( "2013-06-16 23:59:59",rs.dateFrom))  END

        WHEN rs.dateFrom < "2013-06-01 00:00:00" AND rs.dateTo > "2013-06-16 23:59:59"
        THEN DATEDIFF( "2013-06-16 23:59:59", "2013-06-01 00:00:00")+1 

        ELSE 0 END 

 ) AS days       
FROM reservation AS rs
WHERE rs.reservationStatus IN (2,3)
GROUP BY rs.Vehicle_id

问题是查询没有正确计算总天数。有人可以帮我解决这个问题吗？

下面是我的代码示例

http://sqlfiddle.com/#!2/f6cbc/3用于数据测试

Answer 1

间隔没有重叠(即对于一辆车，你有一些逻辑来确保它不会被多次预订？

如果您正在寻找整个历史记录，您当然可以只总结每个预订的个别长度。你想要一些每月的分割吗？

SELECT SUM( TO_DAYS(dateTo)-TO_DAYS(dateFrom) +1 ) AS sum_days_reserved FROM table