How to perform 1 position offset SQL query?

Question

I’m trying to perform a query where in one of the columns (COTA_DIA_ANTERIOR) return me the value of the previous day. But it is bringing the value of the day.

SELECT 
    ET.DT_REFERENCIA
   ,ET.VL_PU 
   ,(SELECT TOP (1) [DT_REFERENCIA]  FROM [TRUST].[dbo].[MTM_RFIXA] WHERE [NR_ATIVO] = ET.NR_ATIVO AND DT_REFERENCIA < ET.DT_REFERENCIA ORDER BY DT_REFERENCIA DESC) as DIA_ANTERIOR                                                                
   ,(SELECT TOP(1) ET.VL_PU  FROM MTM_RFIXA WHERE  DT_REFERENCIA  =  (SELECT TOP (1) [DT_REFERENCIA]  FROM [TRUST].[dbo].[MTM_RFIXA] WHERE [NR_ATIVO] = ET.NR_ATIVO AND DT_REFERENCIA < ET.DT_REFERENCIA ORDER BY DT_REFERENCIA DESC) as COTA_DIA_ANTERIOR

FROM MTM_RFIXA ET

WHERE
NR_ATIVO = '2002'

GROUP BY DT_REFERENCIA, VL_PU
ORDER BY DT_REFERENCIA DESC 

Answer 1

For those who use SQL Server have two very interesting functions to be used in this type of query. They are LEAD and LAG. In SQL Server it stores the last and next line of the query and through these functions we can access them.

LEAD: Take the next line;

LAG: Returns the previous line.

Well, I made an example here using a table similar to the one you have and I believe that due to the decreasing ordering I needed to use LEAD to bring the results. Take the example:

SELECT ET.DT_REFERENCIA
      , LEAD(ET.VL_PU OVER(ORDER BY ET.DT_REFERENCIA DESC)) AS ValorAnterior
      , LEAD(ET.DT_REFERENCIA OVER(ORDER BY ET.DT_REFERENCIA DESC)) AS DataAnterior

      FROM MTM_RFIXA ET
     WHERE NR_ATIVO = '2002'

You can also do some tests by replacing LEAD with LAG and changing query ordering.

Source: LEAD and LAG function

NOTE: If you are using another database for queries, I change my answer.