CODEWITHSUNDEEP
sqlsql-servert-sqlwhere-clause
H.Ben
H.Ben

Reputation: 117

TSQL : conditional query

I am trying to find a way to get results where the Occupancy in the latest Inspection_date and the one before the last are not equal.

In this example only number RoomID 2 will be the only result because: the Occupancy for OrderID 201 = 'Vacant' <> to the Occupancy for OrderID 202 = 'Occupied'.

I have the beginning of the query but cannot seem to find a good logic to end the query.

| RoomID | OrderID | Occupancy | rn |
+--------+---------+-----------+----+
| 01     | 101     | Vacant    | 1  |
| 01     | 102     | Vacant    | 2  |
| 01     | 103     | Occupied  | 3  |
| 01     | 104     | Vacant    | 4  |
| 02     | 201     | Vacant    | 1  |
| 02     | 202     | Occupied  | 2  |
| 02     | 203     | Vacant    | 3  |
| 03     | 301     | Occupied  | 1  |
| 03     | 302     | Occupied  | 2  |
| 03     | 303     | Occupied  | 3  |
| 03     | 304     | Occupied  | 4  |
| 04     | 401     | Occupied  | 1  |
| 04     | 402     | Occupied  | 2  |
| 04     | 403     | Vacant    | 3  |
| 04     | 404     | Occupied  | 4  |


SELECT i.room_number, order_number, Occupancy , row_number() OVER(PARTITION BY room_number ORDER BY Inspection_date DESC) rn 
FROM #inspection_data i

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

In SQL Server 2012+, you can use lag(), so something like this:

SELECT i.*
FROM (SELECT i.room_number, order_number, Occupancy ,
             ROW_NUMER() OVER (PARTITION BY room_number ORDER BY Inspection_date DESC) as seqnum,
             LAG(Occupancy) OVER (PARTITION BY room_number ORDER BY Inspection_date) as prev_Occupancy
      FROM #inspection_data i
     ) i
WHERE prev_Occupancy <> Occupancy AND seqnum = 1 ;

Upvotes: 1

Related Questions