[Programmers] 자동차 대여 기록에서 대여중/ 대여 가능 여부 구분하기

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

프로그래머스

 

SELECT CAR_ID,
       IF(T2.S >0, '대여중', '대여 가능') AVAILABILITY
FROM (
    SELECT CAR_ID, SUM(D) S 
    FROM (
            SELECT *, 
                   IF(START_DATE<='2022-10-16' AND END_DATE>='2022-10-16', 1, 0) D
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
         ) T
    GROUP BY CAR_ID ) T2
ORDER BY CAR_ID DESC

 

 

 

SELECT CAR_ID,
MAX(CASE WHEN '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

SELECT CAR_ID, 
    CASE 
        WHEN CAR_ID IN 
        (
            SELECT CAR_ID
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
            WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE
        ) THEN '대여중'
        ELSE '대여 가능'
    END AS AVAILABILITY
FROM 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY 
    CAR_ID
ORDER BY 
    CAR_ID DESC;