TSQL : conditional query

Question

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

Gordon Linoff · Accepted Answer · 2016-09-30 23:56:31Z

1

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 ;

answered Sep 30, 2016 at 23:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

TSQL : conditional query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related