Hamza Bensaid Hamza Bensaid - 5 months ago 9
SQL Question

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 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


I have tried it this way, it works with the example but is not working with my data .. the logic is not complete:

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


Here is the schema:

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

Answer

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

Comments