H.Ben H.Ben - 2 months ago 6
SQL Question

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

Answer

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 ;