TSQL : The Top Records Of A Given Partition (conditional)

I need to get all the records in TABLE_A where at least the 2 last Status are vacant (relative to Inspection_Date) and the Room_ID does not exist in TABLE_B. This is a simplified t

Solution 1:

PLAIN

1. For each room in TABLE_A select the last date (as lastDate)

2. for each room in TABLE_A select previous date (as prevLastDate)

3. Get room_ids from lastDate which has the status 'vacant' (as lastDateVacant)

4. Get room_ids from prevLastDate which has the status 'vacant' (as prevLastDateVacant)

5. Filter TABLE_A to have only IDs which are there in lastDateVacant and prevLastDateVacant (inner)

6. Filter TABLE_A to have only IDs which are not in TABLE_B (left outer + IS NULL)

As the result you have:

WITH lastDate AS (
    SELECT room_id AS room,MAX(inspection_date) AS date
    FROM "TABLE_A"
    GROUP BY room_id
), prevLastDate AS (
    SELECT room_id AS room,MAX(inspection_date) AS date
    FROM "TABLE_A" a
    INNER JOIN lastDate ON a.room_id = lastDate.room and a.inspection_date < lastDate.date
    GROUP BY room_id
), lastDateVacant AS (
    SELECT room_id AS room FROM "TABLE_A"
    WHERE (room_id,inspection_date) IN (
        SELECT room, date FROM lastDate
    ) AND status = 'vacant' 
), prevLastDateVacant AS (
    SELECT room_id AS room FROM "TABLE_A"
    WHERE (room_id,inspection_date) IN (
        SELECT room, date FROM prevLastDate
    ) AND status = 'vacant' 
)

SELECT a.* FROM "TABLE_A" a 
INNER JOIN lastDateVacant 
    ON a.room_id = lastDateVacant.room
INNER JOIN prevLastDateVacant 
    ON a.room_id = prevLastDateVacant.room
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

Window Function

Not sure if the syntax for TSQL is the same, but here is the shorter variant:

1. Ranking with partion by room and or order by date

2. Check for IDs with rank 1 and 2 having 'vacant' status, grouping by ID and having them occured more than once

WITH room AS ( select room from ( select room_id as room,status,inspection_date as date, RANK() OVER (PARTITION BY room_id ORDER BY inspection_date DESC) AS RANK from "TABLE_A" ) where (rank in ( 1,2) and status = 'vacant') group by room having count() > 1 ) SELECT a. FROM "TABLE_A" a INNER JOIN room ON a.room_id = room.room 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

Solution 2:

Your conditions translate almost directly into a query. You can use window functions for the vacant count and not exists for the relationship to table_b:

select a.*
from (select a.*,
             sum(case when status = 'vacant' then 1 else 0 end) over (partition by room_id) as num_vacant
      from table_a a
      where not exists (select 1
                        from table_b b
                        where b.room_id = a.room_id
                       )
    ) a
where num_vacant >= 2;

EDIT:

If you want the last two to be vacant, you can do find that last record that is non-vacant and then count the ones bigger than that:

select a.*
from (select a.*,
             sum(case when a2.max_nonvacant > a.inspection_date then 0 else 1) over (partition by room_id) as num_vacant_last
      from table_a a outer apply
           (select max(inspection_date) as max_nonvacant
            from table_a a2
            where a2.room_id = a.room_id and a2.status <> 'vacant'
           ) a2
      where not exists (select 1
                        from table_b b
                        where b.room_id = a.room_id
                       )
    ) a
where num_vacant_last >= 2;

Solution 3:

This worked for me and I have checked again and again.

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

Solution 4:

I did this test: extracts all the room_id that considering the last two Status (equal status) in relation to the inspection_date (descending order):

select * from TABLE_A WHERE [Room_Id] IN
(
    SELECT [Room_Id] FROM 
    (SELECT ROW_NUMBER() OVER(PARTITION BY [Room_Id] ORDER BY [Inspection_Date] DESC ) AS id,   
      [Room_Id],[Status],[Inspection_Date]
      FROM TABLE_A 
      ) AA
    WHERE AA.ID <=2
--selecting the last two Inspection_Date
 and [Status] = 'vacant'  
            GROUP BY [Room_Id],[Status] HAVING COUNT(*) >1          
) 
AND 
[Room_Id] NOT IN (SELECT Room_Id FROM TABLE_B)
order by Room_Id, Inspection_Date desc