https://school.programmers.co.kr/learn/courses/30/lessons/157340
시작 날짜 ~ 2022-10-16 ~ 반납 날짜
이런 형식으로 BETWEEN으로 해당하는 CAR_ID들을 서브쿼리로 추출한 후
CASE WHEN을 활용하여 풀었다.
SELECT CAR_ID, (CASE WHEN CAR_ID IN
(SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE '2022-10-16' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m-%d') AND DATE_FORMAT(END_DATE, '%Y-%m-%d'))
THEN '대여중'
ELSE '대여 가능'
END) AS "AVAILABILITY"
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC