151141.md

프로그래머스 151141

'자동차 대여 기록 별 대여 금액 구하기'

🔗 https://school.programmers.co.kr/learn/courses/30/lessons/151141

SELECT 
    H.HISTORY_ID,
    FLOOR(
        (DATEDIFF(H.END_DATE, H.START_DATE)+1)
        * C.DAILY_FEE
        * (IFNULL(100-P.DISCOUNT_RATE, 100)) / 100
    ) AS FEE
    
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON C.CAR_TYPE = P.CAR_TYPE
AND P.DURATION_TYPE = CASE 
            WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 90 THEN '90일 이상'
            WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 30 THEN '30일 이상'
            WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 7 THEN '7일 이상'
        END
WHERE C.CAR_TYPE LIKE '트럭'
ORDER BY FEE DESC, H.HISTORY_ID DESC;