1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 코드를 입력하세요
WITH ## 2022-11-01~2022-11-30 까지 대여가능한 차량
ABLE_CAR AS (
SELECT  DISTINCT CAR_ID
  FROM  CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CRH
 WHERE  NOT EXISTS (
    SELECT 1
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS R
    WHERE R.CAR_ID = CRH.CAR_ID
      AND R.START_DATE <= '2022-11-30'
      AND R.END_DATE >= '2022-11-01')
)
,
##30일 이상 할인율
DIS_PLAN AS(
SELECT  *
  FROM  CAR_RENTAL_COMPANY_DISCOUNT_PLAN
 WHERE  DURATION_TYPE = '30일 이상'
)


SELECT  AC.CAR_ID
        ,C.CAR_TYPE AS CAR_TYPE
        ,ROUND((C.DAILY_FEE * (1 - DP.DISCOUNT_RATE*0.01) * 30),0) AS FEE
  FROM  ABLE_CAR AS AC
  JOIN  CAR_RENTAL_COMPANY_CAR AS C
    ON  AC.CAR_ID = C.CAR_ID
  JOIN  DIS_PLAN AS DP
    ON  C.CAR_TYPE = DP.CAR_TYPE
 WHERE  C.CAR_TYPE IN ('SUV','세단') 
        AND (C.DAILY_FEE * (1 - DP.DISCOUNT_RATE*0.01) * 30) > 500000
        AND (C.DAILY_FEE * (1 - DP.DISCOUNT_RATE*0.01) * 30) < 2000000
 ORDER
    BY  FEE DESC, CAR_TYPE ASC, CAR_ID DESC
;