FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

WITH Guest AS
(
	SELECT 1 AS ID, 'One'   AS FNAME UNION ALL
  	SELECT 2 AS ID, 'Two'   AS FNAME UNION ALL
  	SELECT 3 AS ID, 'Three' AS FNAME  
),

Reserve AS
(
    SELECT 1 AS ID, 1 AS GID, 1 AS HID, 100 AS RNO, '20240902' AS CHECK_IN_DATE UNION ALL
  	SELECT 2 AS ID, 1 AS GID, 1 AS HID, 100 AS RNO, '20240903' AS CHECK_IN_DATE UNION ALL
  	SELECT 3 AS ID, 1 AS GID, 1 AS HID, 120 AS RNO, '20240902' AS CHECK_IN_DATE UNION ALL
  	SELECT 4 AS ID, 1 AS GID, 2 AS HID, 100 AS RNO, '20240903' AS CHECK_IN_DATE UNION ALL
  	SELECT 5 AS ID, 2 AS GID, 1 AS HID, 100 AS RNO, '20240902' AS CHECK_IN_DATE UNION ALL
  	SELECT 6 AS ID, 2 AS GID, 1 AS HID, 100 AS RNO, '20240903' AS CHECK_IN_DATE 
)

SELECT DISTINCT M.FNAME
FROM
(
    SELECT DR.FNAME
         , MAX(DR.UniqueRoomRank) OVER(PARTITION BY DR.GID, DR.HID, DR.RNO) MaxUniqueRoomRank
    FROM
    (
        SELECT G.FNAME
             , R.*
             , DENSE_RANK() OVER(PARTITION BY R.GID, R.HID, R.RNO ORDER BY R.CHECK_IN_DATE) AS UniqueRoomRank
        FROM  Reserve R
        INNER JOIN  Guest G
        	ON R.GID = G.ID
        WHERE R.CHECK_IN_DATE BETWEEN '20240901' AND '20240909'
    ) DR
) M
WHERE M.MaxUniqueRoomRank > 1

FNAME
One
Two

(2 rows affected)