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

• 문제
  
• 정답

SELECT H.HISTORY_ID, FLOOR(
    (DATEDIFF(H.END_DATE, H.START_DATE)+1) *C.DAILY_FEE * COALESCE
    (
        (SELECT (1 - P.DISCOUNT_RATE * 0.01)
         FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
         WHERE P.CAR_TYPE = C.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일 이상'
         ELSE NULL
         END),1)) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
JOIN CAR_RENTAL_COMPANY_CAR C USING(CAR_ID)
WHERE C.CAR_TYPE LIKE '%트럭'
ORDER BY FEE DESC, H.HISTORY_ID DESC

• 결과