Query with MAX (DATE)

Question

Query with MAX (DATE)

Navigation

#1 by (0 votes)

2

I'm trying to generate the query below as follows.

Cliente     Tarefa  Data da Ultima Providencia
Teste        16555       04-07-2017

However, it is generating in a duplicate way, that is, I want to get the last date of the last provision, however, it is bringing all dates even using max .

Cliente     Tarefa  Data da Ultima Providencia
Teste        16555       04-07-2017 
Teste        16555       02-06-2017

SELECT uc.usurazaosocial [cliente - razão social],
       uc.usunome [cliente - nome fantasia],
       t.tarid [tarefa],
       ur.usunome [responsável - tarefa],
       t.tartitulo [título],
       MAX(CONVERT(date, a.ativdata, 103))[data última providência],
       ISNULL(uta.usunome, utg.usunome) [responsável tramite],
       dbo.dias_uteis(MAX(CONVERT(date, a.ativdata, 103)), GETDATE()) [dias sem movimentação],
       t.tarnumativ [número da providência],
       s.statusdesc [status]
  FROM tarefa t
       LEFT JOIN usuario uc ON uc.usuid = t.usuidcliente
       LEFT JOIN usuario ur ON ur.usuid = t.usuidresponsavel
       LEFT JOIN atividade a ON a.tarid = t.tarid
       LEFT JOIN usuario uta ON uta.usuid = a.usuidalteracao
       LEFT JOIN usuario utg ON utg.usuid = a.usuid
       LEFT JOIN status s ON s.codstatus = t.tarstatus
 WHERE t.projid IS NOT NULL
   AND a.ativorigem = 'S'
   AND t.tarid = 161574
 GROUP BY uc.usurazaosocial,
          uc.usunome,
          t.tarid,
          t.tartitulo,
          t.tarnumativ,
          s.statusdesc,
          ur.usunome,
          uta.usunome,
          utg.usunome
 ORDER BY uc.usurazaosocial DESC

sql sql-server query

asked by anonymous 18.09.2017 / 21:22

1 answer

Render a policy API for crypto-currency quotes? [closed]

score 0 · Accepted Answer

Simplify your query . Use NOT EXISTS to check if it's the last date:

SELECT uc.usurazaosocial AS razao_social,
       uc.usunome AS nome_fantasia,
       t.tarid AS tarefa,
       CONVERT(date, a.ativdata, 103) AS ultima_providencia
  FROM tarefa t
       INNER JOIN atividade a ON a.tarid = t.tarid
       LEFT JOIN usuario uc ON uc.usuid = t.usuidcliente
 WHERE t.projid IS NOT NULL
   AND a.ativorigem = 'S'
   AND t.tarid = 161574
   AND NOT EXISTS(SELECT 1
                    FROM atividade a2
                   WHERE a2.tarid = a.tarid
                     AND a2.ativdata > a.ativdata)