1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
|
SELECT DISTINCT
(NAME.TITLE || ' ' || RESERVATION_NAME.SGUEST_FIRSTNAME || ' ' || RESERVATION_NAME.SGUEST_NAME) AS client_nom,
DECODE(LANGUAGE,
'F', 'FRANCE',
'E', 'ANGLETERRE'
) AS client_pays,
DECODE(OPERA.RESERVATION_NAME.RESORT,
'OPERA', 'Best Western Premier Opera Diamond',
'BATIGN', 'Opera Batignolles Hotel'
) AS client_hotel,
TO_CHAR(TRUNC_ACTUAL_CHECK_IN_DATE,'DD/MM/YYYY') AS hotel_date_entree,
TO_CHAR(TRUNC_ACTUAL_CHECK_OUT_DATE,'DD/MM/YYYY') AS hotel_date_sortie,
LOWER(PHONE_NUMBER) AS client_email,
RESORT_ORIGINS_OF_BOOKING.DESCRIPTION AS origine_reservation,
TRUNC( SYSDATE - TO_DATE(TRUNC_ACTUAL_CHECK_OUT_DATE)) AS NB_JOURS
FROM OPERA.RESERVATION_NAME
JOIN OPERA.NAME ON RESERVATION_NAME.NAME_ID = NAME.NAME_ID
JOIN OPERA.NAME_PHONE ON RESERVATION_NAME.NAME_ID = NAME_PHONE.NAME_ID
JOIN OPERA.RESERVATION_SUMMARY ON RESERVATION_NAME.RESV_NAME_ID = RESERVATION_SUMMARY.EVENT_ID
JOIN OPERA.RESORT_ORIGINS_OF_BOOKING ON (RESERVATION_SUMMARY.RESORT = RESORT_ORIGINS_OF_BOOKING.RESORT AND RESERVATION_SUMMARY.SOURCE_CODE = RESORT_ORIGINS_OF_BOOKING.SOURCE_CODE)
WHERE RESV_STATUS = 'CHECKED OUT'
AND TRUNC( SYSDATE - TO_DATE(TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0
--AND TRUNC( SYSDATE - TO_DATE(TRUNC_ACTUAL_CHECK_OUT_DATE)) = 1
AND SGUEST_NAME IS NOT NULL
AND NAME_PHONE.PHONE_TYPE = 'EMAIL'
AND NAME_PHONE.PHONE_NUMBER IS NOT NULL
AND RESERVATION_SUMMARY.EVENT_TYPE = 'R'
ORDER BY CLIENT_HOTEL ASC, CLIENT_NOM ASC |
Partager