H.Ben H.Ben - 1 month ago 18
SQL Question

T-SQL : partitioning using a case statement

I have the following table :

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


I need to pull my data on a
RoomID
level where the
Occupancy
= 'Occupied' and
Status
= 'Preservation' in any instance of a given
RoomID
.

The result should look like the following:

| RoomID | Flag |
+--------+---------+
| 01 | 1 |
| 02 | 0 |
| 03 | 1 |
| 04 | 0 |


I have an impression that this is easy but I cannot see it at the moment, thank you in advance for your help !

vkp vkp
Answer

You can use conditional aggregation.

select roomid,
count(distinct case when Occupancy = 'Occupied' and Status = 'Preservation' then 1 end) flag
from tablename
group by roomid
Comments