Query in SQL SERVER 2017

Question

Query in SQL SERVER 2017

Navigation

#1 by (1 votes)
#2 by (0 votes)

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 last interaction date) and Empresa ; I need this information only for calls that are open, I used the filter ( where ) in the sta_codigo = '1' field, which means that the call is open, because the number 1 represents the open call code. However, when I try to do select , it only brings all calls open, with all interactions, however I only want the last interaction that the call that it has open had.

Here's the select I've done:

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

sql-server select

asked by anonymous 02.08.2018 / 13:58

2 answers

0

The code worked with Sorack's guidance. The code looks 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

02.08.2018 / 15:29

Exit command in PL / SQL Text larger than the button

score 1 · Accepted Answer

You only need to use the EXISTS clause together with NOT in WHERE to determine that you want only the interaction that does not have or with a date greater:

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 subquery is presented with the keyword EXISTS , the subquery functions as an existence test. The WHERE clause of the outer query tests whether the rows returned by the subquery exist. The subquery does not actually produce any data; it returns a value of TRUE or FALSE .