SQL Compare values in other rows

Question

I have a table called transferencia that has data on the movement of a guest in a hotel, for example

The table has no date of departure, so the date of departure is the data_entrada in which the transferencia is equal to transferencia + 1.

I need to make a query that returns an extra column with the date of exit described in the paragraph above and in case the last transferencia be equal to null, should return the current number of days, considering the current date of the consultation (in this case today, that is why the guest is still staying). If the value of the last record of transferencia be it <> null, is why the guest has already left and the system has calculated the amount of days he stayed.

My DBDS is Firebird 2.5

Answer 1

You can use a subquery to search for the next record. This way also there is no danger of breaking the logic if there is a "hole" in the sequences:

SELECT t.*,
       (SELECT FIRST 1 t2.data_entrada
          FROM tranferencia t2
         WHERE t2.codigo_hospede = t.codigo_hospede
           AND t2.transferencia > t.transferencia
         ORDER BY t2.transferencia) AS data_saida
  FROM transferencia t

Answer 2

Answering my own question, with the help of Sorack, further increasing the question of counting the days if the guest stays.

select transf.codigo_hospede, transf.transferencia, transf.data_entrada, transf.quarto, 
iif(transf.qtde_dias is null,  DATEDIFF(day, transf.data_entrada, current_date),transf.qtde_dias),
from (
       SELECT t.*,
           (SELECT FIRST 1 t2.data_entrada
             FROM tranferencia t2
             WHERE t2.codigo_hospede = t.codigo_hospede
             AND t2.transferencia > t.transferencia
             ORDER BY t2.transferencia) AS data_saida
        FROM transferencia t) as transf