[String, Date/LEVEL4] 자동차 대여 기록 별 대여 금액 구하기 (MySQL/Oracle)

MySQL

SELECT HISTORY_ID
, ROUND(DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1)
    * (CASE  
        WHEN DATEDIFF(END_DATE,START_DATE)+1 < 7 then 1
        WHEN DATEDIFF(END_DATE,START_DATE)+1 < 30 then 0.95
        WHEN DATEDIFF(END_DATE,START_DATE)+1 < 90 then 0.92
        ELSE 0.85 END)) AS "FEE"
FROM  CAR_RENTAL_COMPANY_CAR A
    , CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    , CAR_RENTAL_COMPANY_DISCOUNT_PLAN C
WHERE A.CAR_ID = B.CAR_ID
AND A.CAR_TYPE = C.CAR_TYPE
AND A.CAR_TYPE = '트럭'
GROUP BY HISTORY_ID
ORDER BY FEE DESC, HISTORY_ID DESC

 

 

---

Oracle

WITH HIST AS (
SELECT B.HISTORY_ID AS HISTORY_ID
     , A.CAR_ID AS CAR_ID
     , A.CAR_TYPE AS CAR_TYPE
     , A.DAILY_FEE AS DAILY_FEE
     , B.DURATION AS DURATION
     , CASE WHEN B.DURATION < 7 THEN ''
            ELSE CASE WHEN B.DURATION < 30 THEN '7일 이상'
            ELSE CASE WHEN B.DURATION < 90 THEN '30일 이상'
            ELSE '90일 이상'
            END END END AS DURATION_TYPE
  FROM CAR_RENTAL_COMPANY_CAR A
     , (SELECT HISTORY_ID
             , CAR_ID
             , END_DATE - START_DATE + 1 AS duration
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) B
 WHERE 1 = 1
   AND A.CAR_ID = B.CAR_ID
   AND A.CAR_TYPE = '트럭')

SELECT H.HISTORY_ID
     , H.DAILY_FEE * H.DURATION * (100 - NVL(A.DISCOUNT_RATE, 0)) / 100 AS FEE
  FROM HIST H
      , CAR_RENTAL_COMPANY_DISCOUNT_PLAN A
  WHERE H.CAR_TYPE = A.CAR_TYPE(+)
    AND H.DURATION_TYPE = A.DURATION_TYPE(+)
 ORDER BY H.DAILY_FEE * H.DURATION * (100 - NVL(A.DISCOUNT_RATE, 0)) / 100 desc, H.HISTORY_ID desc

-- 임시 테이블 쓰지 않고 최대한 해보려다가 결국 임시테이블 사용하였고, 다른 분의 답안을 참고하였다. 나중에 좀 더 천천히 봐야 겠음. (출처 : https://school.programmers.co.kr/questions/42339)

-- 날짜 계산할 때는 항상 +1 하는 것 잊지 말기. 

-- 할인 퍼센트 관련 계산할 때 원금 * (100 - (할인비율 / 100)) 계산 법 잊지 말기.