Query in SQL SERVER 2017

Asked

Viewed 88 times

1

I need to perform a query in SQL Server 2017, where I need to bring the following information: Codigo do chamado, Data Abertura, Interação, Analista, UltimaInteração(would be the date of the last interaction) and Empresa; need this information only of the calls that are open, I used the filter(where) in the field sta_codigo = '1' which means that the call is open, because the number 1 represents the open call code. However, when I try to realize the select, it brings only all open calls, with all interactions, but I just want the last interaction that the call that is open had.

Follow the select I made:

select c.cha_codigo [Codigo do chamado], c.cha_dtabertura as [Data 
Abertura],
Convert(varchar(5000),i.int_descricao) as [Interação], u.usu_nome 
[Analista], 
r.int_dtinteracao as UltimaInteração,
e.emp_fantasia as [Empresa]
from (select distinct max(int_dtinteracao) as int_dtinteracao from 
interacoes) as r, interacoes as i
inner join chamados as c on c.cha_codigo = i.cha_codigo
inner join usuario as u on  u.usu_cod  = i.log_codigo 
inner join empresa as e on e.emp_codigo = c.emp_codigo
where c.sta_codigo = '1'
group by i.cha_codigo, c.cha_codigo, c.cha_dtabertura,  
Convert(varchar(5000), i.int_descricao), u.usu_nome, e.emp_fantasia, 
r.int_dtinteracao
Order By c.cha_codigo desc

Resultado da query

sql-server select

  • You will use the MAX. See more here Operators Max, Min, Count, Update and Delete with Subqueries - Union and Subquery

  • 2

    Besides what @Rbz commented, who reads in your question "Using the open call filter" you have no idea what this is. . you commented something you know, but for us who are reading it does not make sense, you would need to explain better and clearly put the table structure. Anyway, this you solve in the where, and to bring only the one value, in the case "the last", you can use max

  • Ola Ricardo, I’m sorry for not explaining it right, I’m already tidying up.

  • Ola Rbz, I have already performed the MAX in Where, however it brings only 1 record, I need the select bring all open calls, represented by c.sta_code = '1' where 1 is the code of the calls that are open, and of those open calls, bring only the last interaction, because the calls can have 'n' interactions, but I just want the last.

2 answers

1


You only need to use the clause EXISTS together with NOT in the WHERE to determine that it wants only the interaction that it does not have or with longer date:

SELECT c.cha_codigo [Codigo do chamado]
      -- DEMAIS CAMPOS CAMPOS
  FROM chamados c
      INNER JOIN interacoes i ON i.cha_codigo ON i.cha_codigo
      -- DEMAIS JOINS
WHERE c.sta_codigo = '1'
  AND NOT EXISTS(SELECT 1
                    FROM interacoes i2
                  WHERE i2.cha_codigo = c.cha_codigo
                    AND i2.int_dtinteracao > i.int_dtinteracao)

EXISTS

When a sub-quota is displayed with the keyword EXISTS, the subconsultation acts as a test of existence. The clause WHERE of the external query tests whether the lines returned by the sub-query exist. The sub-query does not actually produce any data; it returns a value TRUE or FALSE.

  • It worked Sorack, thank you very much. !

  • @Joãopedrofernandes Don’t forget to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

0

The code worked with Sorack’s guidance. The code went like this:

select c.cha_codigo [Codigo do chamado], c.cha_dtabertura as [Data Abertura],
Convert(varchar(5000),i.int_descricao) as [Interação], u.usu_nome [Analista], 
i.int_dtinteracao as UltimaInteração,
e.emp_fantasia as [Empresa]
from interacoes as i
inner join chamados as c on c.cha_codigo = i.cha_codigo
inner join usuario as u on  u.usu_cod  = i.log_codigo 
inner join empresa as e on e.emp_codigo = c.emp_codigo
where c.sta_codigo = '1' AND NOT EXISTS
(Select 1 from interacoes i2 where i2.cha_codigo = c.cha_codigo and i2.int_dtinteracao > i.int_dtinteracao)
group by i.cha_codigo, c.cha_codigo, c.cha_dtabertura,  Convert(varchar(5000), i.int_descricao), u.usu_nome, e.emp_fantasia, 
i.int_dtinteracao
Order By c.cha_codigo desc

Browser other questions tagged sql-server select

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