[SQL] Programmers - 자동차 대여 기록별 대여 금액 구하기

Problem

Solution

 출력해야 하는것
 HISTORY_ID (대여기록ID), 대여기록별 대여금액 (DAILY_FEE * 대여기간 * DISCOUNT)AS FEE

 고려해야 하는것
 CAR_TYPE이 '트럭'
 대여기간에 따른 할인비율이 다름. 이것에 관련된 쿼리문을 작성해야함. SELECT 절에 CASE구문을 사용?

 DURATION_TYPE이라는 컬럼을 사용해서 타입별 할인폭을 선택 이건 문자열에 '7일 이상' 또는 '30일 이상' 이 있는 지 확인해서 맞다면 해당 할인율을 적용하면 될것같음.

 

할인율 적용 이전의 금액 계산

DATEDIFF(END_DATE, START_DATE) * DAILY_FEE

할인 적용 이후의 금액 계산

DATEDIFF(END_DATE, START_DATE) * (DAILY_FEE) * (100-DISCOUNT_RATE/100) 

 

 정렬조건
 대여금액 기준 내림차순, 대여금액이 같을 경우 대여기록ID 기준으로 내림차순

 (ORDER BY FEE DESC, HISTORY_ID DESC;)

 

정렬조건 같은 경우는 별 문제가 없을 것이지만 대여기간별 할인에 관한 쿼리문이 빠져있다.

이 쿼리는 GROUP BY HISTORY_ID로 그룹화 되어있는 각각의 할인금액이 적용된 요금을 뽑아내는 부분이 없다.

 

CASE WHEN THEN으로 대여기간별 할인 비율을 SELECT 해서 FEE 계산식에 넣어버리자

※ 참고로 주의사항은 FEE의 경우 정수부분만 출력되어야 하기때문에 결과를 ROUND나 CEIL으로 감싸야함

 

CASE
        # 7일미만의 DURATION_TYPE 인 경우 할인 없음
        WHEN DATEDIFF(END_DATE, START_DATE)+1< 7 
        THEN CEIL(DAILY_FEE * (DATEDIFF(END_DATE, START_DATE)+1))
        
        # 7일 이상의 DURATION_TYPE, CAR_TYPE = '트럭'
        WHEN DATEDIFF(END_DATE, START_DATE)+1< 30
        THEN 
        CEIL(
            DAILY_FEE * 
            (DATEDIFF(END_DATE, START_DATE)+1) * 
            (
                SELECT (100-DISCOUNT_RATE)/100
                FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                WHERE CAR_TYPE = '트럭' AND DURATION_TYPE ='7일 이상'
            )
        )
    
        # 30일 이상, 90일 미만의 DURATION_TYPE, AND CAR_TYPE = '트럭'
        WHEN DATEDIFF(END_DATE, START_DATE)+1 BETWEEN 30 AND 89
        THEN 
        CEIL(
            DAILY_FEE * 
            (DATEDIFF(END_DATE, START_DATE)+1) * 
            (
                SELECT (100-DISCOUNT_RATE)/100
                FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                WHERE CAR_TYPE = '트럭' AND DURATION_TYPE ='30일 이상'
            )
        )
        # 90일 이상의 DURATION TYPE, AND CAR_TYPE = '트럭'
        WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 90
        THEN 
        CEIL(
            DAILY_FEE * 
            (DATEDIFF(END_DATE, START_DATE)+1) * 
            (
                SELECT (100-DISCOUNT_RATE)/100
                FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                WHERE CAR_TYPE = '트럭' AND DURATION_TYPE ='90일 이상'
            )
        )
    END AS FEE

이 부분을 추가한 쿼리문은 아래와 같다.

 

# 출력해야 하는것
# HISTORY_ID (대여기록ID), 대여기록별 대여금액 (DAILY_FEE * 대여기간 * DISCOUNT)AS FEE

# 고려해야 하는것
# CAR_TYPE이 '트럭'
# 대여기간에 따른 할인비율이 다름. 이것에 관련된 쿼리문을 작성해야함. SELECT 절에 CASE구문을 사용?

# 정렬조건
# 대여금액 기준 내림차순, 대여금액이 같을 경우 대여기록ID 기준으로 내림차순(ORDER BY FEE DESC, HISTORY_ID DESC;)


SELECT 
    HISTORY_ID, 
    # CAR_TYPE, 
    # DAILY_FEE, 
    # DATEDIFF(END_DATE, START_DATE)+1 AS RENTAL_PERIOD,
    # (DAILY_FEE * (DATEDIFF(END_DATE, START_DATE)+1)) AS TOTAL_FEE_BEFORE_DISCOUNT,
    CASE
        # 7일미만의 DURATION_TYPE 인 경우 할인 없음
        WHEN DATEDIFF(END_DATE, START_DATE)+1< 7 
        THEN CEIL(DAILY_FEE * (DATEDIFF(END_DATE, START_DATE)+1))
        
        # 7일 이상의 DURATION_TYPE, CAR_TYPE = '트럭'
        WHEN DATEDIFF(END_DATE, START_DATE)+1< 30
        THEN 
        CEIL(
            DAILY_FEE * 
            (DATEDIFF(END_DATE, START_DATE)+1) * 
            (
                SELECT (100-DISCOUNT_RATE)/100
                FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                WHERE CAR_TYPE = '트럭' AND DURATION_TYPE ='7일 이상'
            )
        )
    
        # 30일 이상, 90일 미만의 DURATION_TYPE, AND CAR_TYPE = '트럭'
        WHEN DATEDIFF(END_DATE, START_DATE)+1 BETWEEN 30 AND 89
        THEN 
        CEIL(
            DAILY_FEE * 
            (DATEDIFF(END_DATE, START_DATE)+1) * 
            (
                SELECT (100-DISCOUNT_RATE)/100
                FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                WHERE CAR_TYPE = '트럭' AND DURATION_TYPE ='30일 이상'
            )
        )
        # 90일 이상의 DURATION TYPE, AND CAR_TYPE = '트럭'
        WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 90
        THEN 
        CEIL(
            DAILY_FEE * 
            (DATEDIFF(END_DATE, START_DATE)+1) * 
            (
                SELECT (100-DISCOUNT_RATE)/100
                FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                WHERE CAR_TYPE = '트럭' AND DURATION_TYPE ='90일 이상'
            )
        )
    END AS FEE
FROM CAR_RENTAL_COMPANY_CAR c
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON h.CAR_ID = c.CAR_ID
WHERE CAR_TYPE = '트럭'
GROUP BY HISTORY_ID
ORDER BY FEE DESC, HISTORY_ID DESC;

많이 지저분한데 현업에서 일하는 친구에게 도움을 받아서 깔끔하게 정리할 수 있는 방법을 찾아보자.