Different price on weekdays and weekends

Question

I have a table Orders that holds information about the rental date range and the price for a car rental.

The price is given by:

SELECT ROUND((points * (SELECT DATEDIFF('$dateTo', '$dateFrom'))) * 0.95) AS price

$dateTo and $dateFrom are PHP variables that holds the value of a given date range by the user. In others words:

$dateTo = $_POST['date-to'];
$dateFrom = $_POST['date-from'];

So for each day you are given 5% off from the rental price, but I also want to check if the date range goes across a Saturday or a Sunday. If the rental is from Friday to Monday, I want to add an extra 10 points to the total price because the date range includes a Saturday.

Is this possible?

I'm a bit confused about what you mean by "*If the rental is from Friday to Monday, I want to add an extra 10 points to the total price because the date range includes a Saturday*". Why until Monday and not until Sunday? What if they rent it on Friday for 1 day? What if they rent starting Saturday and return it the following day? — Mike, May 02 '16 at 18:39
@Mike - They just want a query or code which can take a date range and determine whether or not a weekend day falls in that range. If that happens they need to add 10 to the price. They're just giving a specific example in this situation. The problem is "add 10 to the price" is a little too general, at least for me. Do you want the database entry to get modified so you need an update? Do you want only what's displayed to be modified? What is actually being impacted by this change? — Dresden, May 02 '16 at 18:41
@MikeS You could be right, but it doesn't look like a specific example to me. — Mike, May 02 '16 at 18:42
@MikeS Looks to me like the `price` column in the `Orders` table is calculated based on the given formula. — Mike, May 02 '16 at 18:44
Yeah that looks like the case so it's not actually being stored anywhere it's just the results of a query they've already created. So the actual price portion would have to change if they wanted to do it in the query. I'm just wondering if it's better to do it that way. Because you can use php to do something like this: http://stackoverflow.com/questions/9553731/checking-if-date-falls-on-weekday-or-weekend — Dresden, May 02 '16 at 18:45
So, in essence, the question is: does a given date range overlap a weekend? If so, can a car not be rented between the 9th & 17th? — Strawberry, May 02 '16 at 19:24
It appears the OP is missing in action here. @robert If you're going to post a question at least respond to the comments. — Mike, May 02 '16 at 19:54

score 0 · Answer 1 · answered May 02 '16 at 21:00

You could use a stored Procedure for that purpose where your query may be nested inside of a Loop to check if it' s Weekday or not. Alternatively, You could check if the range of Dates has a Weekend within it using PHP like so:

<?php 

    /**
     * FUNCTION THAT CHECKS IF A WEEKEND EXISTS BETWEEN 2 GIVEN DATES
     * @param string $startDate 
     * @param string $endDate 
     * @return boolean
     */
    function dateRangeContainsWeekend($startDate, $endDate) {
        $tsDate  = strtotime($startDate);
        $diff    = date_diff(date_create($startDate), date_create($endDate));
        $dDiff   = $diff->days;

        if ($dDiff > 0) {
            for ($i = 0; $i < $dDiff; $i++) {
                $timeDiff  = 24 * 60 * 60 * $i;
                $sDate     = $tsDate + $timeDiff;
                $weekDay   = date('w', $sDate);

                if (($weekDay == 0 || $weekDay == 6)) {
                    return ($weekDay == 0 || $weekDay == 6);
                }
            }
        }
        return false;
    }

    // TESTS - - - -
    $d1 = "2016-05-02";
    $d2 = "2016-05-10";
    var_dump( dateRangeContainsWeekend($d1, $d2) );  // DUMPS true

    // NOW; IF YOU KNOW THAT THE GIVEN DATES CONTAIN A WEEKEND,
    // YOU MAY MODIFY YOUR QUERY TO REFLECT YOUR DESIRES 
    $factor = "0.95";  //CHOOSE YOUR FACTOR HERE...
    $sql    = "SELECT ROUND((points * (SELECT DATEDIFF('$dateTo', '$dateFrom'))) * $factor) AS price";

Different price on weekdays and weekends

1 Answers1