[프로그래머스] 자동차 대여 기록 별 대여 금액 구하기 - MySQL

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

프로그래머스

---

•  할인 받기 전

SELECT h.history_id as HISTORY_ID, round((datediff(h.end_date,h.start_date)+1)*c.daily_fee) as FEE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY  as h 
join CAR_RENTAL_COMPANY_CAR as c
on h.CAR_ID = c.CAR_ID
where c.CAR_TYPE = '트럭'
order by FEE desc, HISTORY_ID desc;

• 대여 기간이 7일 이상이 안되면 할인X
• CAR_RENTAL_COMPANY_DISCOUNT_PLAN에 7일 이하에 대한 정보가 없기에 LEFT JOIN

SELECT h.history_id as HISTORY_ID, round((datediff(h.end_date,h.start_date)+1)*c.daily_fee*(100-ifnull(p.discount_rate,0))/100) as FEE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY  as h 
join CAR_RENTAL_COMPANY_CAR as c
on h.CAR_ID = c.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(END_DATE,START_DATE)+1) >= 90 then '90일 이상'
                             when (datediff(END_DATE,START_DATE)+1) >= 30 then '30일 이상'
                             when (datediff(END_DATE,START_DATE)+1) >= 7 then '7일 이상'
                             end)
where c.CAR_TYPE = '트럭'
order by FEE desc, HISTORY_ID desc;