TSQL - Logic driven by two most recent rows, Sql Server

TSQL - Logic driven by two most recent rows

CREATE TABLE TABLE_A (Room_Id int, Status varchar(55), Inspection_Date date);

INSERT INTO TABLE_A (Room_Id, Status, Inspection_Date)
VALUES  (1, 'vacant',      '4/17/2016'),
        (1, 'vacant',      '5/15/2015'),
        (1, 'occupied',    '12/16/2015'),
        (2, 'occupied',    '5/21/2015'),
        (2, 'vacant',      '1/19/2016'),
        (3, 'vacant',      '8/27/2015'),
        (3, 'vacant',      '12/12/2015'),
        (3, 'vacant',      '3/22/2016'),
        (4, 'occupied',    '2/2/2015'),
        (4, 'vacant',      '3/24/2015'),
        (4, 'vacant',      '3/25/2016'),
        (5, 'vacant',      '5/21/2015'), -- I've created a fifth set of rows which will throw off the logic in the second query
        (5, 'vacant',      '1/19/2016'),
        (5, 'occupied',    '3/24/2016');

CREATE TABLE TABLE_B (Room_Id int, Status varchar(55), Inspection_Date date);

INSERT INTO TABLE_B (Room_Id, Status, Inspection_Date)
VALUES
        (1, 'vacant',      '5/15/2015'),
        (2, 'occupied',    '5/21/2015'),
        (2, 'vacant',      '1/19/2016'),
        (1, 'vacant',      '12/16/2015'),
        (1, 'vacant',      '4/17/2016');
        
-- this query matches the second except that it correctly excludes room 5
with Rooms as (
    select
        Room_Id, Status,
        row_number() over (partition by Room_Id order by Inspection_Date desc) as rn
    from TABLE_A
), Candidates as (
    select Room_Id from Rooms group by Room_Id
    having sum(case when rn in (1, 2) and Status = 'vacant' then 1 else null end) = 2
)
select * from TABLE_A
where Room_Id in (select Room_Id from Candidates except select Room_Id from TABLE_B)
order by Room_Id, Inspection_Date desc;

-- this second query will erroneously include room 5 and it will also duplicate those rows in the output
WITH lastDate AS (
    SELECT Room_ID, MAX(Inspection_Date) AS [date] FROM TABLE_A GROUP BY Room_ID
), prevLastDate AS (
    SELECT a.Room_ID, MAX(Inspection_Date) AS [date]
    FROM TABLE_A a INNER JOIN lastDate ON a.Room_ID = lastDate.Room_ID and a.Inspection_Date < lastDate.[date]
    GROUP BY a.Room_ID
), lastDateVacant AS (
    SELECT Room_ID FROM TABLE_A
    WHERE Room_ID IN (SELECT Room_ID FROM lastDate) -- redundant - all Room_IDs will be in this table
        AND Inspection_Date IN (SELECT [date] FROM lastDate) 
        AND Status = 'Vacant'
), prevLastDateVacant AS (
    SELECT Room_ID FROM TABLE_A
    WHERE Room_ID IN (SELECT Room_ID FROM prevLastDate) 
        AND Inspection_Date IN (SELECT [date] FROM prevLastDate) 
        AND Status = 'Vacant'
)
SELECT a.*
FROM TABLE_A a 
    INNER JOIN lastDateVacant 
        ON a.Room_ID = lastDateVacant.Room_ID
    INNER JOIN prevLastDateVacant 
        ON a.Room_ID = prevLastDateVacant.Room_ID
    LEFT OUTER JOIN TABLE_B AS b
        ON a.Room_ID = b.Room_ID    
WHERE b.Room_ID IS NULL 
ORDER BY a.Room_ID ASC, a.Inspection_Date DESC;


run \| edit \| history \| help	0