CODEWITHSUNDEEP
sqlt-sqlpartitioningflags
H.Ben
H.Ben

Reputation: 117

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 !

Upvotes: 0

Views: 50

Answers (2)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

You can also use the below query using UNION.

;with cte_1
 AS
( SELECT DISTINCT RoomId
  FROM YourTable
  WHERE Occupancy='Occupied' AND Status='Predervation')
  SELECT RoomId,1 Status
  FROM cte_1
  UNON
  SELECT DISTINCT RoomId,0 Status
   FROM YourTable t
   WHERE NOT EXISTS(SELECT 1 FROM cte_1 c
               WHERE t.RoomId=c.RoomId)

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 1

Related Questions