title: T-SQL : partitioning using a case statement
body: <p>I have the following table : </p>
  
  <pre><code>| RoomID | OrderID | Occupancy | Status        |
  +--------+---------+-----------+---------------+
  | 01     | 101     | Vacant    | inspection    |
  | 01     | 102     | Occupied  | Preservation  |
  | 01     | 103     | Occupied  | inspection    |
  | 01     | 104     | Vacant    | inspection    |
  | 02     | 201     | Vacant    | inspection    |
  | 02     | 202     | Occupied  | inspection    |
  | 02     | 203     | Vacant    | inspection    |
  | 03     | 301     | Vacant    | inspection    |
  | 03     | 302     | Occupied  | inspection    |
  | 03     | 303     | Occupied  | Preservation  |
  | 03     | 304     | Occupied  | Preservation  |
  | 04     | 401     | Occupied  | inspection    |
  | 04     | 402     | Occupied  | inspection    |
  | 04     | 403     | Vacant    | Preservation  |
  | 04     | 404     | Occupied  | inspection    |
  </code></pre>
  
  <p>I need to pull my data on a <code>RoomID</code> level where the <code>Occupancy</code> = 'Occupied' and <code>Status</code> = 'Preservation' in any instance of a given <code>RoomID</code>.  </p>
  
  <p>The result should look like the following: </p>
  
  <pre><code>| RoomID | Flag    |
  +--------+---------+
  | 01     | 1       |
  | 02     | 0       |
  | 03     | 1       |
  | 04     | 0       |
  </code></pre>
  
  <p>I have an impression that this is easy but I cannot see it at the moment, thank you in advance for your help ! </p>