Bring the word HISTORICAL when the line date is less than the previous dates,

Question

Bring the word HISTORICAL when the line date is less than the previous dates,

Asked 7 years, 7 months ago

Viewed 147 times

0

I have a query where I should register the lines with error with the word Historical, the lines that bring the date LESS than any of the dates of the previous lines should present the word HISTORICO, (In the same Item)

Can you help? the column in question is TYPE,

the result should be like the table below.

Tb_import [cod_title,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

Which database are you using? Could you post the query code you have already been able to create? Could post the structure of the tables you need to query (if possible share in sqlfidle)?

– Camilo Santos

2017/05/24 at 01:40
Hello Friend , I confess that this is my first post and I’m still learning, the BD is Sqlserver, the information comes all from a single table, I set the example above for fear of posting data of the company I work, but if I can’t give an idea with them, i can try to assemble something different, I’m studying the posts yet to learn how to use sqlfidle,

– Alexandre Gonçalves

2017/05/24 at 01:43
Just because of this information you posted makes it hard to help. Try to put more information about the problem, or some query you’ve already done. I don’t quite understand it

– Isaias Tavares

2017/05/24 at 02:28
@Alexandregonçalves: What is the version of SQL Server? // To evaluate the sequence in column [Date] it is necessary that each row has a unique identification, which allows to obtain the expected order. How can you identify the order of the rows? In the example you posted there are repeated rows for the CD/Item/Vala/Valb columns.

– José Diz

2017/05/24 at 09:37
@Josédiz , Thanks for the attention, in the rush I ended up posting wrong, sql server 2014, The CD column is unique, it doesn’t repeat, Can we get an idea? Tkss

– Alexandre Gonçalves

2017/05/24 at 17:39
@Josédiz, A coluna onde traz histórico, seria um Case When, ou um select aninhado? I still have difficulties, I’m learning,

– Alexandre Gonçalves

2017/05/24 at 17:41
@Alexandregonçalves: Assuming the sequence of times 4:50, 4:10, 4:30, 5:10, which values should be marked as HISTORICO: only 4:10 or 4:10 and 4:30?

– José Diz

2017/05/24 at 20:16
@Josédiz Thank you very much for your attention, I spent all day training your examples, the code example #1 worked perfectly getting this way: SELECT top 100 [GRU_CD] ,[GRU_PF] as 'Crane' ,[GRU_DT_REGISTRO] as 'Registry' ,[GRU_DS_DATUM] ,case when [GRU_DT_REGISTRO] < lag([GRU_DT_REGISTRO]) over ( Order By [GRU_CD] ,[GRU_PF]) then 'HISTORICO' Else '0' end as Type FROM [KLABIN_SGL]. [dbo]. [TB_GRUA] Where [GRU_PF] in ('TE0048') AND DATEPART(mm,[GRU_DT_REGISTRO]) = 5 Order By [GRU_CD] ,[GRU_PF]

– Alexandre Gonçalves

2017/05/26 at 02:23
@Josédiz however I need something that works like in code #2, He should check ALL previous items, and if the current line is smaller, then he should bring the word Historico, Assuming the time sequence 4:50, 4:10, 4:30, 5:10 in this case should mark as Historico the items 4:10 & 4:30 taking into account the Item and CD, :

– Alexandre Gonçalves

2017/05/26 at 02:27
@Josédiz Segue, SELECT TOP 100 [GRU_CD] ,[GRU_PF] ,[GRU_DT_REGISTRO] , case exists when ( Select * From [KLABIN_SGL]. [dbo]. [TB_GRUA] as T2 Where T2.GRU_CD < T1.GRU_CD And T2. [GRU_DT_REGISTRO] > T1. [GRU_DT_REGISTRO]) Then 'Hist' Else '' end as Type FROM [KLABIN_SGL]. [dbo]. [TB_GRUA] as T1 Where [GRU_PF] in ('TE0048') AND DATEPART(mm,[GRU_DT_REGISTRO]) = 5 Order By [GRU_CD] ,[GRU_PF] ?

– Alexandre Gonçalves

2017/05/26 at 02:31
@Josédiz for example on this line, He should mark HIST 2 x, following his example 2 on the lines 1532579 TE0048 2017-05-01 01:35:51.000 0
1532600 TE0048 2017-05-01 01:36:51.000 0
1532618 TE0048 2017-05-01 01:31:51.000 HISTORICO -- Here 1532654 TE0048 2017-05-01 01:35:05.000 0 Here 1532699 TE0048 2017-05-01 01:39:51.000 0 1532703 TE0048 2017-05-01 01:40:51,000 0

– Alexandre Gonçalves

2017/05/26 at 02:36

Show 6 more comments

1 answer

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.

by José Diz • **6,942** points · Answer 1 · 2017-05-24T19:58:27+00:00

This is an approach that analyses 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 it is necessary to analyze only the immediately previous line, the function LAG 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;

UPDATING
Considering comments posted in the topic, it seems to me that the query is not for GRU_CD but for GRU_PF + GRU_CD.

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

-- 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;

Note that the domain of the two Select should be the same. Accordingly, any change to the external WHERE clause should be reflected in the WHERE clause of the SELECT that is in the EXISTS function.