CODEWITHSUNDEEP
mysql
Jaylen
Jaylen

Reputation: 40291

How to calculate the total days for reservation in MySQL

I have a table the stores vehicles reservations for each record I have dateFrom "the reservation start date" and a dateTo "the reservation end date"

I am trying to write a query to calculate the total of day each vehicles was booked and also the total of revenue each vehicles generates.

The business rule are are the following

  1. If the dateFrom and dateTo are in the same day then that is considered 1 days
  2. If the reservation is from 2013-05-25 to 2013-06-06 then then 7 days go to the month of may and 5 days go to the month of june here is a break down of this logic
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)

This is an example on how the calculation should work.

For the revenue I am suppose to calculate the average daily rent by dividing the total revenue by the total rented days then multiplying the daily average by the total day that fits this range

This is my current query but it is not calculating the today days correctly. so in the above example if we assume that the total revenue for the whole reservation is $1500 then the average daily rent is $1500/12 = $125

so since the range that we are calculating is "2013-06-01 00:00:00" to "2013-06-16 23:59:59", the this vehicle should display total days are 5 and total revenue is $625. More, if the range was 2013-05-01 00:00:00 to 2013-05-31 23:59:59 then the same vehicle will have total of 7 days and total revenue of $875

The following is my current query in where I am trying to calculate the differences.

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

The problem is that the query is not calculating the total days correctly. Can somebody help me with this please?

The following is a sample of my code

http://sqlfiddle.com/#!2/f6cbc/3 for testing with data

Upvotes: 3

Views: 1277

Answers (2)

Brian Hoover
Brian Hoover

Reputation: 7991

So I've taken a stab at it. Let's start with the code. The explanation comes later.

SELECT rs.vehicle_id,
  round(sum(datediff(least(rs.dateTo, '2013-06-02 00:00:00'),
                 greatest(rs.dateFrom, '2013-05-31 00:00:00'))*(totalRent + totalTax)*datediff(least(rs.dateTo, '2013-06-02 00:00:00'),
                 greatest(rs.dateFrom, '2013-05-31 00:00:00'))/totalDays))
        AS income,
  sum(datediff(least(rs.dateTo, '2013-06-02 00:00:00'),
                 greatest(rs.dateFrom, '2013-05-31 00:00:00')))
        AS days
FROM reservation rs
  WHERE rs.dateFrom < '2013-06-02 00:00:00'
    AND rs.dateTo > '2013-05-31 00:00:00'
GROUP BY rs.vehicle_id;

SQL Fiddle

The most important thing that you have to think about is whether the date range is inclusive or exclusive. So, if you want the amount from 5/31 to 6/1, does this mean that you want to include revenue from 6/1 or does this mean that you are only looking for revenue on 5/31. This concept trips a lot of developers up. From your comments and posts, I'm assuming that you want the date range to be exclusive.

In the case I created, the window goes from 5/31 at 00:00:00 to 6/1 at 00:00. If you want it to be inclusive, you could end the window at 6/2 00:00:00.

The rest of the solution is just a little more elegant way of doing what you were already doing. You can look for a date window by seeing if the start date is less than the end of the date range and the end date is greater than the start of the date range.

Then you can calculate the actual amount of days in the window by looking at the maximum of the dateFrom and the windowStart, as well as the minimum of the dateTo and the windowEnd.

Upvotes: 1

apartridge
apartridge

Reputation: 1820

There is no overlap in intervals(i.e. for a car you have some logic to ensure that it is not reserved multiple times?

If you are looking for the entire history, you can certainly just sum up the invidual lengths of each reservation. Do you want some per-month breakdown?

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

Upvotes: 1

Related Questions