SQL - 자동차 대여 기록 별 대여 금액 구하기_String, Date

 1. 문제

*문제 원본은 '문제링크' 참고

 2. 학습 내용

1) SELECT 절 내 조건부 컬럼 조회 (조건에 맞춰 FEE값 출력하는 방식으로 접근함)

2) INNER JOIN을 이용한 데이터 조회

🔎 3. 작성 코드

-- 코드를 입력하세요
SELECT b.HISTORY_ID, 
FLOOR(a.DAILY_FEE * (1 - (CASE
    WHEN DATEDIFF(b.END_DATE, b.START_DATE)+1 >= 90 THEN (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = a.CAR_TYPE AND DURATION_TYPE = '90일 이상') / 100
    WHEN DATEDIFF(b.END_DATE, b.START_DATE)+1 >= 30 THEN (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = a.CAR_TYPE AND DURATION_TYPE = '30일 이상') / 100
    WHEN DATEDIFF(b.END_DATE, b.START_DATE)+1 >= 7 THEN (SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = a.CAR_TYPE AND DURATION_TYPE = '7일 이상') / 100
    ELSE 0
END))) * (DATEDIFF(b.END_DATE, b.START_DATE)+1) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS a
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS b ON a.CAR_ID = b.CAR_ID
WHERE a.CAR_TYPE = '트럭'
ORDER BY FEE DESC, b.HISTORY_ID DESC;