WITH RoomAmount AS ( ( SELECT booking.id AS booking _ _ id , , COALESCE ( (
Fantastic news! We've Found the answer you've been seeking!
Question:
WITH RoomAmount AS (
SELECT
booking.id AS booking_id,
COALESCE(SUM(rc.base_price * DATEDIFF(day, booking.checkin_date, booking.checkout_date)),0) AS room_amount
FROM
booking
JOIN
booking_room br ON booking.id = br.booking_id
JOIN
room r ON br.room_id = r.id
JOIN
room_class rc ON r.room_class_id = rc.id
GROUP BY
booking.id
),
AddonAmount AS (
SELECT
booking.id AS booking_id,
COALESCE(SUM(a.price),0) AS addon_amount
FROM
booking
LEFT JOIN
booking_addon ba ON booking.id = ba.booking_id
LEFT JOIN
addon a ON ba.addon_id = a.id
GROUP BY
booking.id
)
UPDATE booking
SET booking_amount =
(
SELECT COALESCE(SUM(room_amount),0)+ COALESCE(SUM(addon_amount),0)
FROM RoomAmount
FULL JOIN AddonAmount ON RoomAmount.booking_id = AddonAmount.booking_id
WHERE booking.id = RoomAmount.booking_id
); can you expalin this query in details ? also is there better way to do it in more comprehensive way ?
Related Book For
Advanced Financial Accounting
ISBN: 978-0078025624
10th edition
Authors: Theodore E. Christensen, David M. Cottrell, Richard E. Baker
Posted Date: