How to calculate the total days for reservation in MySQL -
i have table stores vehicles reservations each record have datefrom "the reservation start date" , dateto "the reservation end date"
i trying write query calculate total of day each vehicles booked , total of revenue each vehicles generates.
the business rule are following
- if
datefrom,datetoin same day considered 1 days - if reservation
2013-05-252013-06-06then 7 days go month of may , 5 days go month of june here break down of 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 example on how calculation should work.
for revenue suppose calculate average daily rent dividing total revenue total rented days multiplying daily average total day fits range
this current query not calculating today days correctly. in above example if assume total revenue whole reservation $1500 average daily rent $1500/12 = $125
so since range calculating "2013-06-01 00:00:00" "2013-06-16 23:59:59", vehicle should display total days 5 , total revenue $625. more, if range 2013-05-01 00:00:00 2013-05-31 23:59:59 same vehicle have total of 7 days , total revenue of $875
the following current query in trying calculate differences.
select rs.vehicle_id, round(sum( case when (rs.datefrom between "2013-06-01 00:00:00" , "2013-06-16 23:59:59") , (rs.dateto between "2013-06-01 00:00:00" , "2013-06-16 23:59:59") (rs.totalrent + rs.totaltax) when rs.dateto between "2013-06-01 00:00:00" , "2013-06-16 23:59:59" , rs.datefrom < "2013-06-01 00:00:00" ( ( (rs.totalrent + rs.totaltax) / case when datediff( rs.dateto,rs.datefrom) = 0 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" , "2013-06-16 23:59:59" , rs.dateto > "2013-06-16 23:59:59" ( ( (rs.totalrent + rs.totaltax) / case when datediff( rs.dateto,rs.datefrom) = 0 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" , rs.dateto > "2013-06-16 23:59:59" ( ( (rs.totalrent + rs.totaltax) / case when datediff( rs.dateto,rs.datefrom) = 0 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 )) income, sum( case when (rs.datefrom between "2013-06-01 00:00:00" , "2013-06-16 23:59:59") , (rs.dateto between "2013-06-01 00:00:00" , "2013-06-16 23:59:59") case when datediff( rs.dateto,rs.datefrom) = 0 1 else datediff( rs.dateto,rs.datefrom) end when rs.dateto between "2013-06-01 00:00:00" , "2013-06-16 23:59:59" , rs.datefrom < "2013-06-01 00:00:00" case when datediff(rs.dateto, "2013-06-01 00:00:00") = 0 1 else (datediff(rs.dateto, "2013-06-01 00:00:00")) end when rs.datefrom between "2013-06-01 00:00:00" , "2013-06-16 23:59:59" , rs.dateto > "2013-06-16 23:59:59" case when datediff( "2013-06-16 23:59:59",rs.datefrom) = 0 1 else (datediff( "2013-06-16 23:59:59",rs.datefrom)) end when rs.datefrom < "2013-06-01 00:00:00" , rs.dateto > "2013-06-16 23:59:59" datediff( "2013-06-16 23:59:59", "2013-06-01 00:00:00")+1 else 0 end ) days reservation rs rs.reservationstatus in (2,3) group rs.vehicle_id the problem query not calculating total days correctly. can me please?
the following sample of code
http://sqlfiddle.com/#!2/f6cbc/3 testing data
there no overlap in intervals(i.e. car have logic ensure not reserved multiple times?
if looking entire history, can sum invidual lengths of each reservation. want per-month breakdown?
select sum( to_days(dateto)-to_days(datefrom) +1 ) sum_days_reserved table
Comments
Post a Comment