How to perform shifted SQL query from 1 position?

Question

How to perform shifted SQL query from 1 position?

Navigation

#1 by (0 votes)

1

I am trying to perform a query where in one of the columns ( COTA_DIA_ANTERIOR ) returns me the value of the previous day. But it's 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

sql query

asked by anonymous 09.07.2018 / 22:58

1 answer

How do I style child components through parent components? Laravel: Access relationship attributes

score 0 · Accepted Answer

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

LEAD: Get 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 had to use the LEAD to bring the results. See 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 testing, replacing the LEAD with LAG and changing the query sort.

Source: Function LEAD and LAG

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