Databases
StackOverflow2013
Postsdbo
POHow to calculate the total days for reservation in MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to calculate the total days for reservation in MySQL
    primarykey
    data
    text
    <p>I have a table the stores vehicles reservations for each record I have <code>dateFrom</code> "the reservation start date" and a <code>dateTo</code> "the reservation end date"</p> <p>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.</p> <p>The business rule are are the following</p> <ol> <li>If the <code>dateFrom</code> and <code>dateTo</code> are in the same day then that is considered 1 days</li> <li>If the reservation is from <code>2013-05-25</code> to <code>2013-06-06</code> 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</li> </ol> <blockquote> <pre><code>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) </code></pre> </blockquote> <p>This is an example on how the calculation should work.</p> <p>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 </p> <p>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 <code>$1500/12 = $125</code></p> <p>so since the range that we are calculating is <code>"2013-06-01 00:00:00"</code> to <code>"2013-06-16 23:59:59"</code>, the this vehicle should display total days are 5 and total revenue is $625. More, if the range was <code>2013-05-01 00:00:00</code> to <code>2013-05-31 23:59:59</code> then the same vehicle will have total of 7 days and total revenue of $875</p> <p>The following is my current query in where I am trying to calculate the differences.</p> <pre><code>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 &lt; "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 &gt; "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 &lt; "2013-06-01 00:00:00" AND rs.dateTo &gt; "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 &lt; "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 &gt; "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 &lt; "2013-06-01 00:00:00" AND rs.dateTo &gt; "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 </code></pre> <p>The problem is that the query is not calculating the total days correctly. Can somebody help me with this please?</p> <p>The following is a sample of my code </p> <p><a href="http://sqlfiddle.com/#!2/f6cbc/3" rel="nofollow">http://sqlfiddle.com/#!2/f6cbc/3</a> for testing with data</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USVenkateshwaran Selvaraj
    1. USJaylen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to calculate the total days for reservation in MySQL
      1. USJaylen
      1. VTBountyStart
    2. VO
      singulars
      1. POHow to calculate the total days for reservation in MySQL
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POHow to calculate the total days for reservation in MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre you looking for one number, the monthly rent for a specific car for a specific month, or multiple numbers, the rent, by month, for a specific car?
      singulars
      1. POHow to calculate the total days for reservation in MySQL
      1. USBrian Hoover
    2. COOne number per vehicle per range. so for vehicle #1 i need the total reserved days with in the selected range and for the same vehicles the total revenue. so if I have total of 10 vehicles then I should get a total of 10 records
      singulars
      1. POHow to calculate the total days for reservation in MySQL
      1. USJaylen
    3. COI'm taking a stab at this, but I'm still having a tough time trying to visualize the problem that you're describing. You've discussed several issues, splitting rent between May and June, counting the number of days, calculating the rent/tax for a date range based on an average rent/tax. Could you post some data, and exactly what you would expect to to see from that data? Ideally, you would create the table in sqlFiddle.com
      singulars
      1. POHow to calculate the total days for reservation in MySQL
      1. USBrian Hoover
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload