How to Calculate price between two dates-CodePudding

$pro_id = 23;
$room_id = 10;
$start_date = '2022-10-28';
$end_date = '2022-11-03';

I failed with this query

SELECT id, start_date, end_date, price 
FROM Table 
WHERE `pro_id`= 23 AND rooms_id= 10 AND 
((start_date <= CAST('2022-10-28' AS DATE) AND end_date >= CAST('2022-11-03' AS DATE)) OR
(start_date <= CAST('2022-11-03' AS DATE) AND end_date >= CAST('2022-11-03' AS DATE)) OR
(start_date >= CAST('2022-10-28' AS DATE) AND end_date <= CAST('2022-11-03' AS DATE)))

Price Table: Table

 ---- --------- ------ ------------ ------------ ------- 
| id | pro_id  | room | data_start | data_end   | price |
 ---- --------- ------ ------------ ------------ ------- 
|  1 |      23 |   10 | 2022-10-01 | 2022-10-31 | 35000 |
|  2 |      23 |   10 | 2022-11-01 | 2022-11-30 | 37500 |
 ---- --------- ------ ------------ ------------ -------

I want to Output

 ---- --------- ------ ----------- -------------- 
| id | pro_id  | room | price     | actual price |
 ---- --------- ------ ----------- -------------- 
|  1 |      23 |   10 | 35000 x 4 |     140000   |
|  2 |      23 |   10 | 37500 x 3 |     112500   |
 ---- --------- ------ ----------- --------------

if I book a room from '2022-10-28' to '2022-11-03' for 7 nights then the price will count as 35000 x 4 and 37500 x 3
like 2022-10-28, 29, 30, 31 = 35000 and 2022-11-01, 02, 03 = 37500

Please help me to solve this issue.

CodePudding user response：

In this case, I would suggest doing it on PHP side.

$conn = $em->getConnection();

$pro_id = 23;
$room_id = 10;
$start_date = new \DateTime('2022-10-28');
$end_date = new \DateTime('2022-11-03');

$result = [];

//loop through each day
$currentDate = $start_date;
while ($currentDate <= $end_date) {
    $sql = "SELECT id, pro_id, room, price FROM Table WHERE pro_id = ? AND room = ? AND CAST(? AS DATE) BETWEEN start_date and end_date";
    $stmt = $conn->prepare($sql);
    $stmt->bindValue(1, $pro_id);
    $stmt->bindValue(2, $room_id);
    $stmt->bindValue(3, $currentDate->format('Y-m-d'));
    $data = $stmt->executeQuery()->fetchAssociative();

    // check here if $data IS NOT EMPTY

    if (array_key_exists($data['id'], $result)) {
          $result[$data['id']]['dates'];
        $result[$data['id']]['actual_price'] = $result[$data['id']]['price'] * $result[$data['id']]['dates'];
    } else {
        $result[$data['id']] = $data;
        $result[$data['id']]['dates'] = 1;
        $result[$data['id']]['actual_price'] = $data['price'];
    }

    $currentDate = $currentDate->add(\DateInterval::createFromDateString('1 day'));
}

I used Doctrine ORM connection to test it out on my side. You can query as you wish.

The result array will look like this:

var_export($result);

array(
    1 => array(
        'id' => 1,
        'pro_id' => 23,
        'room' => 10,
        'price' => 35000,
        'dates' => 4,
        'actual_price' => 140000,
    ),
    2 => array(
        'id' => 2,
        'pro_id' => 23,
        'room' => 10,
        'price' => 37500,
        'dates' => 3,
        'actual_price' => 112500,
    ),
)

CodePudding user response：

You can convert the date field data into days, then subtract them, and multiply with price. Try it:

SELECT
pro_id, room, 
price, datediff(start_date, end_date) * price as actual_price
FROM TableName
WHERE 
(
start_date <= CAST('2022-10-28' AS DATE) 
AND
end_date >= CAST('2022-11-03' AS DATE)
)