Hamza Bensaid Hamza Bensaid - 5 months ago 10
SQL Question

Conditional grouping query

I need to get all the

Room_IDs
where the
Status
is different between the last and any prior
Inspection_Date
.

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

**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 occupied 3/22/2016
4 vacant 2/2/2015
4 vacant 3/24/2015


My result should look like this:

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


I tried this but I am honestly not sure if the logic is correct.

Select *
FROM TableX x1
WHERE EXISTS
( SELECT 1
FROM TableX X2
WHERE X2.Room_Id = X1.Room_Id
GROUP BY X2.Room_Id
HAVING max (Status) <> min (Status))

vkp vkp
Answer

Get the first (latest inspection_date) row for each room_id, using row_number function and join it to the remaining rows (for that room) and check to see if the status is different on any of those rows. If different, select the room_id and subsequently all the rows in the table for that room_id.

select * from tablename
where room_id in (
select t1.room_id 
from (select t.*, row_number() over(partition by room_id order by inspection_date desc) rn 
      from tablename t) t1
join tablename t2 on t1.room_id = t2.room_id and t1.rn = 1
where t1.status <> t2.status
)

Sample Demo

Comments