Bring the word HISTORICAL when the Line Date is shorter than the previous dates,

Question

Bring the word HISTORICAL when the Line Date is shorter than the previous dates,

Navigation

#1 by (0 votes)

0

I have a query where I should record the rows with error with the word History, the rows that bring the date LESS than any of the dates of the previous rows should display the word HISTORICAL,

Can you help? the column in question is TYPE,

The result should look like the table below.

Tb_importance [title_code, date, imp_situation]

CD    Item    ValA    ValB    Data                       Tipo
01    A       10      15      2017-05-01 00:01:57.000    0
02    A       10      15      2017-05-01 00:02:15.000    0
03    A       10      15      2017-05-01 00:02:20.000    0
04    A       10      15      2017-05-01 00:03:40.000    0
05    A       10      15      2017-05-01 00:03:35.000    HISTORICO!
06    A       10      15      2017-05-01 00:04:20.000    0
07    A       10      15      2017-05-01 00:04:25.000    0
08    A       10      15      2017-05-01 00:04:50.000    0
09    A       10      15      2017-05-01 00:04:10.000    HISTORICO!
10    A       10      15      2017-05-01 00:04:30.000    0    <-- ??
11    B       10      15      2017-05-01 00:05:10.000    0
12    B       10      15      2017-05-01 00:05:30.000    0
13    B       10      15      2017-05-01 00:05:05.000    HISTORICO!
14    B       10      15      2017-05-01 00:05:50.000    0

sql sql-server

asked by anonymous 24.05.2017 / 03:31

1 answer

Comparing data returned from DB Connection with DIMEP point clock

score 0 · Answer 1

Here's an approach that looks at all the previous lines:

-- código #2
SELECT CD, Item, ValA, ValB, [Data],
       case when exists (SELECT * from Tabela as T2
                           where T2.CD < T1.CD 
                                 and T2.[Data] > T1.[Data])
            then 'HISTORICO!' else '0' end as Tipo
  from Tabela as T1;

But if you need to parse only the immediately preceding row, the LAG function can be used , perhaps generating more efficient execution.

-- código #1
SELECT CD, Item, ValA, ValB, Data,
       case when [Data] < lag([Data]) over (order by CD asc)
            then 'HISTORICO!' else '0' end as Tipo
  from Tabela;

UPDATE
Considering comments posted on the topic, it seems to me that the query is not by GRU_CD but by GRU_PF + GRU_CD.

Here is code update # 2, considering column and table names:

-- código #3 v2
SELECT GRU_PF as Grua, GRU_CD, 
       convert(char(10), GRU_DT_REGISTRO, 103) as Dia,
       convert(char(8), GRU_DT_REGISTRO, 108) as Hora,
       case when exists (SELECT *
                           from KLABIN_SGL.dbo.TB_GRUA as T2 
                           where T2.GRU_PF = T1.GRU_PF
                                 and T2.GRU_CD < T1.GRU_CD 
                                 and T2.GRU_DT_REGISTRO > T1.GRU_DT_REGISTRO)
            then 'Hist' else '' end as Tipo 
  from KLABIN_SGL.dbo.TB_GRUA as T1
  where GRU_PF in ('TE0048')
        and convert(char(6), T1.GRU_DT_REGISTRO, 112) = '201705'
  order by GRU_PF, GRU_CD;

Be aware that the two SELECT domains must be the same. In this way, any change in the external WHERE clause should be reflected in the WHERE clause of the SELECT that is in the EXISTS function.