Consultation with MAX(DATE)

Question

Consultation with MAX(DATE)

Asked 7 years ago

Viewed 1,021 times

2

I am trying to generate the query below as follows.

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

However, is generating duplicate, IE, I want to take the last date of the last provision, however, is bringing all dates even using the 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

Duplicate of where? Recommended [tour].

– UzumakiArtanis

2017/09/18 at 19:27
But you are returning many more fields than the 3 you quote.

– Sorack

2017/09/18 at 19:29
@Stormwind, the task is duplicated, it was to bring only the maximum date.

– Renan Bessa

2017/09/18 at 19:32
@Sorack, I’ve put these three fields in the single demonstrative question. The other fields have the same value, what differs is only the date that in the case was to bring only the first record with the maximum date.

– Renan Bessa

2017/09/18 at 19:33
Boot one DISTINCT shortly after the SELECT and the MAX on the date to test.

– Sorack

2017/09/18 at 19:34
had already done this test @Sorack, but it didn’t work

– Renan Bessa

2017/09/18 at 19:43
What is the primary key of the table atividade?

– Sorack

2017/09/18 at 19:55
@Sorack, it’s the Ativid field

– Renan Bessa

2017/09/18 at 20:02

Show 3 more comments

1 answer

Browser other questions tagged sql database sql-server query

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

by Sorack • **27,430** points · Answer 1 · 2017-09-18T20:13:11+00:00

Simplify your query. Utilize NOT EXISTS to check if it is 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)