Current Value is less than previous-CodePudding

The below one is all the modifications happened for a reservations.

I need to pick up the reservations where the checkout date is less than the previous ones for a reservation Id. Like if the guest changed the reservation and if they preponed the checkout date.

output: - Only 2 because that is the only reservation for which checkout date is less than the previous one.

Query : I tried to pick up the reservations where there is a modifications and assigned a rank but still not sure how to compare the previous one. Can you please suggest how to proceed here.

 SELECT ReservationID
 FROM (
 SELECT ReservationID,RANK() OVER(PARTITION BY ReservationID ORDER BY CheckoutDate Desc) rn
 FROM RESERVATIONS
 WHERE ReservationID IN (
                             SELECT ReservationID
                             FROM (
                                         SELECT ReservationID,COUNT(DISTINCT CheckoutDate)
                                         FROM RESERVATIONS
                                         GROUP BY ReservationID
                                         HAVING COUNT(DISTINCT CheckoutDate) >1
                             )  
                        )
) WHERE rn=1

CodePudding user response：

I think You can try below query having FIRST_VALUE() window function -

SELECT ReservationID
  FROM (SELECT ReservationID,
               CheckoutDate,
               FIRST_VALUE(CheckoutDate) OVER (PARTITION BY ReservationID
                                              ORDER BY TO_DATE(MonthChanged, 'Mon-YY') DESC) upd_CheckoutDate
          FROM RESERVATIONS)
 WHERE upd_CheckoutDate < CheckoutDate;

Demo.