Group lines with null

Question

How can I remove the null lines and leave only the dates next to each other;

Example: 48010255 | 2016-06-10 07:46:41.573 | null | 2016-06-09 12:36:22.243

SELECT
     cod_pista
     ,DT_ENTRADA    = IIF(ST_TIPO_ACAO = 'E' AND CO_DESTINO = 7888,[DATA],NULL)
     ,DT_SAIDA      = IIF(ST_TIPO_ACAO = 'S' AND CO_ORIGEM = 7888,[DATA],NULL)
     ,DT_ENVIO      = IIF(ST_TIPO_ACAO = 'S' AND CO_DESTINO = 7888,[DATA],NULL)
FROM tmp_2 TR

Thank you.

Answer 1

Only with SQL Standard it is not possible to solve this. It is necessary to use analytical functions to analyze the data that will come in the next line, or that came in the previous line.

This is a case to use analytical function LAG of SQL Server.

LAG (scalar_expression [,offset] [,default])  
    OVER ( [ partition_by_clause ] order_by_clause )

The function LAG can "see" the value of the next line of consultation, with this we can make some decisions.

In the case of your consultation, I believe that if you do something like this,:

WITH TR (SELECT id,
                cod_pista,
                DT_ENTRADA = IIF(ST_TIPO_ACAO = 'E' AND CO_DESTINO = 7888, [DATA], NULL),
                DT_SAIDA   = IIF(ST_TIPO_ACAO = 'S' AND CO_ORIGEM  = 7888, [DATA], NULL),
                DT_ENVIO   = IIF(ST_TIPO_ACAO = 'S' AND CO_DESTINO = 7888, [DATA], NULL)
           FROM tmp_2)



SELECT DISTINCT
       cod_pista,
       DT_ENTRADA = ISNULL(DT_ENTRADA, LAG(DT_ENTRADA, 1, 0) OVER (ORDER BY id)),
       DT_SAIDA = ISNULL(DT_SAIDA, LAG(DT_SAIDA, 1, 0) OVER (ORDER BY id)),
       DT_ENVIO = ISNULL(DT_ENVIO, LAG(DT_ENVIO, 1, 0) OVER (ORDER BY id))
 FROM tr

See how this query returns, you will certainly need some design adjustments, but you will already have your data "grouped" in the same line.