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))
Aucun commentaire:
Enregistrer un commentaire