Hamza Bensaid Hamza Bensaid - 6 months ago 17
SQL Question

TSQL: conditional group by query

I need to get all the

Room_IDs
where the
Status
are reported vacant, and then occupied at a later date, only.

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
3 vacant 8/27/2015
3 vacant 12/12/2015
3 occupied 3/22/2016

Answer

Here's one option using exists with a correlated subquery:

select * from yourtable t
where exists (
  select 1
  from yourtable c
  where c.room_id = t.room_id 
  group by c.room_id
  having min(case when status = 'vacant' then inspection_date end) <
         max(case when status = 'occupied' then inspection_date end)
  )