jeudi 7 juillet 2016

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

Aucun commentaire:

Enregistrer un commentaire