T-SQL: Conditional Existential grouping

Question

I need to get all the Room_IDs in TABLE_A where the two 2 or more last Status of each instance of Room_IDs are consecutively vacant (relative to Inspection_Date) and does not exist in TABLE_B.

This is a simplified table I am using as an example:

TABLE_A:

  Room_Id   Status    Inspection_Date
  -------------------------------------
    1        vacant      5/15/2015
    2        occupied    5/21/2015
    2        vacant      1/19/2016
    1        occupied   12/16/2015
    4        vacant      3/25/2016
    3        vacant      8/27/2015
    1        vacant      4/17/2016
    3        vacant     12/12/2015
    3        vacant      3/22/2016
    4        occupied    2/2/2015
    4        vacant      3/24/2015

TABLE_B:

  Room_Id   Status    Inspection_Date
  ------------------------------------
    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

My result should look like this:

   Room_Id  Status  Inspection_Date
   ---------------------------------
    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

Here is the schema:

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

INSERT INTO TABLE_A (`Room_Id`, `Status`, `Inspection_Date`)
VALUES  (1, 'vacant',      '5/15/2015'),
        (2, 'occupied',    '5/21/2015'),
        (2, 'vacant',      '1/19/2016'),
        (1, 'occupied',   '12/16/2015'),
        (4, 'vacant',      '3/25/2016'),
        (3, 'vacant',      '8/27/2015'),
        (1, 'vacant',      '4/17/2016'),
        (3, 'vacant',     '12/12/2015'),
        (3, 'vacant',      '3/22/2016'),
        (4, 'occupied',       '2/2/2015'),
        (4, 'vacant',      '3/24/2015');

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

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'),;

Answer 1

Consider this approach:

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

See the query in operation here: http://rextester.com/VXBRFF91880

Answer 2

all of the answers were correct to a certain extant but never gave me the last two of each instance of Room_IDs as Status vacant:

I was able to find the answer

         WITH lastDate AS ( SELECT Room_ID  ,MAX(Inspection_Date) AS [date]
                        FROM TableA
                        GROUP BY Room_ID), 
          prevLastDate AS ( SELECT a.Room_ID  ,MAX(Inspection_Date) AS [date]
                            FROM TableA 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 TableA
                              WHERE Room_ID IN ( SELECT Room_ID FROM lastDate) 
                              AND Inspection_Date IN ( SELECT [date] FROM lastDate) 
                              AND Status = 'Vacant'),
          prevLastDateVacant AS ( SELECT Room_ID   
                                  FROM TableA
                                  WHERE Room_ID IN ( SELECT Room_ID FROM prevLastDate) 
                                  AND Inspection_Date IN ( SELECT [date] FROM prevLastDate) 
                                  AND Status = 'Vacant')

    SELECT a.* 
    FROM TableA a 
    INNER JOIN lastDateVacant 
        ON a.Room_ID = lastDateVacant.Room_ID
    INNER JOIN prevLastDateVacant 
        ON a.Room_ID = prevLastDateVacant.Room_ID
    LEFT OUTER JOIN preservation.. 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

Answer 3

Another way:

;WITH cte AS (
SELECT DISTINCT a.Room_Id,
        COUNT(a.Inspection_Date) OVER(PARTITION BY a.Room_Id,a.[Status]  ORDER BY a.[Status]) as d
FROM TABLE_A a
FULL OUTER JOIN TABLE_B b
    ON a.Room_Id = b.Room_Id and a.Inspection_Date = b.Inspection_Date
WHERE b.Room_Id IS NULL and a.[Status] = 'vacant'
)

SELECT a.*
FROM cte c
INNER JOIN TABLE_A a
    ON a.Room_Id = c.Room_Id

Output:

Room_Id Status      Inspection_Date
4       vacant      3/25/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

Answer 4

This gives the results you are looking for based on the sample data. ps Thanks for including the create table and insert statements.

With cteA As
(
Select *, Row_Number() Over (Partition By Room_ID, Status Order By Inspection_Date Desc) RowNum From Table_A 
)
Select * From Table_A Where Room_Id In
(
Select Room_Id 
    From cteA
    Where Room_Id Not In (Select Room_Id From Table_B) 
        And Status = 'vacant' And RowNum > 1 
)
    Order By Room_Id, Inspection_Date