Actions
VOC support #2171
closed[VOC]ASAP Date request - Rate+allotment check 1103 & please check my request as below.
Start date:
11/03/2025
Due date:
11/03/2025
% Done:
100%
Estimated time:
3.00 h
Part:
Build env.:
Prod
Description
Dear Team,
Please check below request from Grace:
I would like to request data below.
1. For 30 days: If there is rate & allotment below 10days , please mark it as "No bookable".
--> which mean we have 1/3 has no allotment/rate.
2. For 60days: From 31~60days, If there is rate & allotment below 10days , please mark it as "No bookable".
3. For 90days: From 61~90days, If there is rate & allotment below 10days , please mark it as "No bookable".
Could you please help divide the data into these time segments (0–30 days, 31–60 days, 61–90 days) accordingly?
Also, besides of adding the region/city, there are also duplicate hotels, which you mentioned before since is based on contract, is it possible to include the contract type as well?
Thank you.
Best Regards,
Grace
Files
Updated by Mon Nguyen 4 months ago
set @NOWADAY = NOW;
set @F_R1 = date(@NOWADAY);
set @T_R1 = DATE_ADD(date(@NOWADAY), INTERVAL 30 DAY);
set @F_R3 = date(@NOWADAY);
set @T_R3 = DATE_ADD(date(@NOWADAY), INTERVAL 60 DAY);
set @F_R4 = date(@NOWADAY);
set @T_R4 = DATE_ADD(date(@NOWADAY), INTERVAL 90 DAY);
SELECT A.*
FROM (
SELECT DISTINCT
HM.COUNTRY_CODE
, HM.COUNTRY_NAME AS COUNTRY_NAME
, HM.HOTEL_CODE AS HotelCode
, HM.HOTEL_NAME_EN as HotelName
, (
SELECT IFNULL(VVHM.LEGACY_HOTEL_CODE, VVHM.VENDOR_HOTEL_CODE)
FROM VD_VENDOR_HOTEL_MAPPING VVHM
WHERE VVHM.HOTEL_CODE = HM.HOTEL_CODE
AND VVHM.VENDOR_COMP_CODE = 110000
LIMIT 1
) as AsisHotelCode
, (SELECT DETAIL_CODE_NAME_EN FROM BS_DETAIL_CODE WHERE MASTER_CODE = 'BS018' AND DETAIL_CODE = HM.REGISTER_STATUS_CODE) AS HotelStatus, ( CASE WHEN (
SELECT COUNT(VRDP.APPLIED_DATE)
FROM VD_VENDOR_ROOM_DAILY_PRICE VRDP
INNER JOIN VD_VENDOR_ROOM_PLAN RP ON VRDP.ROOM_PLAN_SEQ = RP.ROOM_PLAN_SEQ
AND VRDP.APPLIED_DATE >= date(@F_R1)
WHERE VRDP.HOTEL_CODE = HM.HOTEL_CODE AND VRDP.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRDP.APPLIED_DATE BETWEEN date(@F_R1) AND date(@T_R1)
AND VRDP.VENDOR_NET_AMOUNT > 0 AND VRDP.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_30_Rate
, ( CASE WHEN (
SELECT COUNT(VRA.APPLIED_DATE)
FROM VD_VENDOR_ROOM_ALLOTMENT VRA
WHERE VRA.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRA.APPLIED_DATE BETWEEN date(@F_R1) AND date(@T_R1)
AND VRA.LEFT_COUNT_SUM > 0 AND VRA.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_30_Allotment, ( CASE WHEN (
SELECT COUNT(VRDP.APPLIED_DATE)
FROM VD_VENDOR_ROOM_DAILY_PRICE VRDP
INNER JOIN VD_VENDOR_ROOM_PLAN RP ON VRDP.ROOM_PLAN_SEQ = RP.ROOM_PLAN_SEQ
AND VRDP.APPLIED_DATE >= date(@F_R3)
WHERE VRDP.HOTEL_CODE = HM.HOTEL_CODE AND VRDP.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRDP.APPLIED_DATE BETWEEN date(@F_R3) AND date(@T_R3)
AND VRDP.VENDOR_NET_AMOUNT > 0 AND VRDP.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_60_Rate
, ( CASE WHEN (
SELECT COUNT(VRA.APPLIED_DATE)
FROM VD_VENDOR_ROOM_ALLOTMENT VRA
WHERE VRA.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRA.APPLIED_DATE BETWEEN date(@F_R3) AND date(@T_R3)
AND VRA.LEFT_COUNT_SUM > 0 AND VRA.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_60_Allotment, ( CASE WHEN (
SELECT COUNT(VRDP.APPLIED_DATE)
FROM VD_VENDOR_ROOM_DAILY_PRICE VRDP
INNER JOIN VD_VENDOR_ROOM_PLAN RP ON VRDP.ROOM_PLAN_SEQ = RP.ROOM_PLAN_SEQ
AND VRDP.APPLIED_DATE >= date(@F_R4)
WHERE VRDP.HOTEL_CODE = HM.HOTEL_CODE AND VRDP.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRDP.APPLIED_DATE BETWEEN date(@F_R4) AND date(@T_R4)
AND VRDP.VENDOR_NET_AMOUNT > 0 AND VRDP.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_90_Rate
, ( CASE WHEN (
SELECT COUNT(VRA.APPLIED_DATE)
FROM VD_VENDOR_ROOM_ALLOTMENT VRA
WHERE VRA.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRA.APPLIED_DATE BETWEEN date(@F_R4) AND date(@T_R4)
AND VRA.LEFT_COUNT_SUM > 0 AND VRA.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_90_Allotment
FROM (
SELECT DISTINCT
HM.COUNTRY_CODE as COUNTRY_CODE,
CC.COUNTRY_NAME_EN AS COUNTRY_NAME
, HM.HOTEL_CODE
, HM.HOTEL_NAME_EN
, HM.REGION_CODE
, RC.REGION_NAME_EN AS REGION_NAME
, HM.REGISTER_STATUS_CODE
, HM.ADDRESS_EN
, HM.PHONE_NO
, HM.STAR_RATING
FROM HO_HOTEL_MASTER HM
INNER JOIN VD_VENDOR_HOTEL_CONTRACT VHC ON VHC.HOTEL_CODE = HM.HOTEL_CODE AND VHC.CONTRACT_STATUS_CODE = 'A'
INNER JOIN BS_COUNTRY_CODE CC ON HM.COUNTRY_CODE = CC.COUNTRY_CODE
LEFT JOIN BS_REGION_CODE RC ON HM.REGION_CODE = RC.REGION_CODE
) HM
INNER JOIN VD_VENDOR_ROOM_TYPE VRT ON VRT.HOTEL_CODE = HM.HOTEL_CODE
AND VRT.SALES_STOP_YN = 'N'
LEFT JOIN BS_COUNTRY_CODE CC ON HM.COUNTRY_CODE = CC.COUNTRY_CODE
LEFT JOIN BS_DETAIL_CODE DC ON HM.REGISTER_STATUS_CODE = DC.DETAIL_CODE
) A
WHERE 1=1
AND (
(
A.Bookable_30_Rate = 'Y'
AND A.Bookable_30_Allotment = 'Y'
)
OR (
A.Bookable_60_Rate = 'Y'
AND A.Bookable_60_Allotment = 'Y'
)
OR (
A.Bookable_90_Rate = 'Y'
AND A.Bookable_90_Allotment = 'Y'
)
)
ORDER BY A.COUNTRY_CODE, A.HotelCode;Updated by Daniel Do 4 months ago
- File 20251103-Check-rate-allotment.xlsx 20251103-Check-rate-allotment.xlsx added
- File #2171-Check-rate-allotment.sql #2171-Check-rate-allotment.sql added
- Due date set to 11/03/2025
- Status changed from 신규(New) to QA test
- Assignee changed from Daniel Do to Tracy Tran
- Start date set to 11/03/2025
- % Done changed from 0 to 100
- Estimated time set to 3.00 h
- Build env. set to Prod
Updated by Tracy Tran 4 months ago
- Status changed from QA test to 완료성공(Resolve)
- Assignee changed from Tracy Tran to Mon Nguyen
Actions
