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

• CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

SELECT history_id
     , round(daily_fee  * 
             (datediff(end_date, start_date) + 1) * 
             COALESCE(1 - (discount_rate * .01),1)
             ,0) AS fee
FROM
(SELECT h.history_id, h.car_id, c.car_type, c.daily_fee, h.end_date, h.start_date,
        CASE WHEN datediff(h.end_date, h.start_date) + 1 BETWEEN 7 AND 29 THEN '7일 이상'
             WHEN datediff(h.end_date, h.start_date) + 1 BETWEEN 30 AND 89 THEN '30일 이상'
             WHEN datediff(h.end_date, h.start_date) + 1 >= 90 THEN '90일 이상'
             ELSE null END AS duration_date
             FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
             JOIN CAR_RENTAL_COMPANY_CAR c 
             ON c.car_id = h.car_id) a
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p 
ON a.car_type = p.car_type 
AND a.duration_date = p.duration_type
WHERE a.car_type = '트럭'
ORDER BY fee DESC, history_id DESC;

① (SELECT h.history_id, h.car_id, c.car_type, c.daily_fee, h.end_date, h.start_date,
        CASE WHEN datediff(h.end_date, h.start_date) + 1 BETWEEN 7 AND 29 THEN '7일 이상'
             WHEN datediff(h.end_date, h.start_date) + 1 BETWEEN 30 AND 89 THEN '30일 이상'
             WHEN datediff(h.end_date, h.start_date) + 1 >= 90 THEN '90일 이상'
             ELSE null END AS duration_date
             FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
             JOIN CAR_RENTAL_COMPANY_CAR c 
             ON c.car_id = h.car_id) a

# 대여 기록 별로 대여 금액을 구해야하기 때문에 

모든 기록들에 대한 대여기간을 구해준다.

# 7일미만일 경우 NULL 값

# CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블의 경우 7일 이상부터 할인율이 있기 때문에 7일 미만 대여기간도 구해주어야 한다.

 

 

② LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p 
ON a.car_type = p.car_type 
AND a.duration_date = p.duration_type

# 대여기간에 대한 정보와 할인율 테이블을 join 시켜주고,

ON 조건절에는 대여기간종류가 일치하는 경우를 포함시켜준다.

 

③ SELECT history_id
     , round(daily_fee  * 
             (datediff(end_date, start_date) + 1) * 
             COALESCE(1 - (discount_rate * .01),1)
             ,0) AS fee

# 일일 대여 요금 * 대여기간 * 할인율

# COALESCE(1 - (discount_rate * .01),1)

> discount_rate 가 5% 일때 * .01 을 하게 되면 0.05

> 1- 0.05 = 0.95

> discount_rate 가 NUL 일때에는 *.01 을 하게 되어도 NULL

> NULL 이 아닌 최초의 값을 구하기 때문에 1 이 들어가게 된다.