[SQL] CASE문 Subquery

SELECT 

H.HISTORY_ID
, ROUND(C.DAILY_FEE * (DATEDIFF(H.END_DATE, H.START_DATE) + 1) * (100 - (
    CASE
        WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 90
        THEN (SELECT DISCOUNT_RATE from CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '90일 이상' AND CAR_TYPE = '트럭')
        → WHEN 또는 THEN에서 서브 쿼리 사용
				WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 30
        THEN (SELECT DISCOUNT_RATE from CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '30일 이상' AND CAR_TYPE = '트럭')
        WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 7
        THEN (SELECT DISCOUNT_RATE from CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE = '7일 이상' AND CAR_TYPE = '트럭')
        ELSE 0
    END
)) * 0.01) FEE

FROM CAR_RENTAL_COMPANY_CAR C
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID=H.CAR_ID
WHERE C.CAR_TYPE='트럭'
ORDER BY 2 DESC, 1 DESC