Eliminar linhas redundantes

[Aloysio Coutinho 0]

Bom dia pessoAll,

Tenho uma query que retorna o seguinte resultado:

Como vocês podem perceber, tenho linhas redundantes que significam que estes "Jobs" foram reexecutados por algum motivo.
Gostaria de trazer nesta tabela apenas a linha que contém a informação de "Rerun" de maior valor.
Não estou conseguindo fazer isso.
Será que vocês poderiam me ajudar?
Detalhe: Todos os dados desta query estão em uma tabela. Apenas o Rerun está em outra. Estou fazendo um "inner join".

A query está aí. É complexa por causa do cálculo de duração, mas meu foco está em eliminar as linhas redundantes:

 

declare @data_inicial varchar(20)
declare @data_final varchar(20)

set @data_inicial = '2019-05-02 00:00:00'
set @data_final = '2019-05-02 23:59:59'

select  H.name as [Job Name],
CASE
WHEN H.status_code = 140 THEN 'FAILED'
WHEN H.status_code = 180 THEN 'SKIPPED'
WHEN H.status_code = 190 THEN 'FINISHED'
WHEN H.status_code = 200 THEN 'SUCCESS'
END as Status,
E.attempt_count as Rerun,
CASE
WHEN LEFT(CONVERT(varchar,H.start_time, 20), 20) between @verao_inicial and @verao_final
THEN CONVERT(varchar(10),DATEADD(hh,-2,H.start_time),103) + ' ' +  CONVERT(varchar(8),DATEADD(hh,-2,H.start_time),108)
WHEN LEFT(CONVERT(varchar,H.start_time, 20), 20) not between @verao_inicial and @verao_final
THEN CONVERT(varchar(10),DATEADD(hh,-3,H.start_time),103) + ' ' +  CONVERT(varchar(8),DATEADD(hh,-3,H.start_time),108)
END as [Data Inicial],
CASE
WHEN LEFT(CONVERT(varchar,H.end_time, 20), 20) between @verao_inicial and @verao_final
THEN CONVERT(varchar(10),DATEADD(hh,-2,H.end_time),103) + ' ' +  CONVERT(varchar(8),DATEADD(hh,-2,H.end_time),108)
WHEN LEFT(CONVERT(varchar,H.end_time, 20), 20) not between @verao_inicial and @verao_final
THEN CONVERT(varchar(10),DATEADD(hh,-3,H.end_time),103) + ' ' +  CONVERT(varchar(8),DATEADD(hh,-3,H.end_time),108)
END as [Data Final],
CASE 
WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos'
WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo'
WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 0)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos '
WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 0)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto '
WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos'
WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo'
WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 0 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos'
WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 0 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo'
---------------------- Hora singular
WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos'
WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo'
WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 0)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos '
WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 0)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto '
WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos'
WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' +
Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo'
WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 0 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos'
WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 0 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo'
WHEN (DATEDIFF(ss, H.start_time, H.end_time) = 3600)
THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora '
---------------- Minutos plural
WHEN DATEDIFF(ss, H.start_time, H.end_time) >= 120 and DATEDIFF(ss, H.start_time, H.end_time) < 3600 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1
THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos'
WHEN DATEDIFF(ss, H.start_time, H.end_time) >= 120 and DATEDIFF(ss, H.start_time, H.end_time) < 3600 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1
THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo'
WHEN DATEDIFF(ss, H.start_time, H.end_time) >= 120 and DATEDIFF(ss, H.start_time, H.end_time) < 3600 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 0
THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos '
---------------- Minuto singular
WHEN DATEDIFF(ss, H.start_time, H.end_time) > 60 and DATEDIFF(ss, H.start_time, H.end_time) < 120 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1
THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos'
WHEN DATEDIFF(ss, H.start_time, H.end_time) > 60 and DATEDIFF(ss, H.start_time, H.end_time) < 120 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1
THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' +
Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo'
WHEN DATEDIFF(ss, H.start_time, H.end_time) = 60
THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto '
WHEN DATEDIFF(ss, H.start_time, H.end_time) > 1 and DATEDIFF(ss, H.start_time, H.end_time) < 60
THEN Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos'
WHEN DATEDIFF(ss, H.start_time, H.end_time) <= 1
THEN Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo'
ELSE
'0'
END as Duração
from dbo.ops_history H inner join dbo.ops_exec E
on H.task_id = E.task_id
where H.name like 'br_cbss%'
and H.start_time is not null
and LEFT(CONVERT(varchar, DATEADD(hh,-3,H.sys_updated_on), 20), 20) between @data_inicial and @data_final
and H.status_code in (140,180,190,200)
group by H.name, H.status_code, E.attempt_count, H.start_time, H.end_time, H.sys_updated_on
order by H.sys_updated_on, E.attempt_count desc

 

De ante mão agradeço a colaboração.

---

Aloysio Coutinho

 

[advaldomesquita 93]

entre suas regras, tente isso:

 

Eliminar Duplicidade

[Marcos Roberto Vieira Juni 0]

declare @teste table (cod int, nome varchar(30))
insert into @teste (cod, nome) values (1, 'Brasil')
insert into @teste (cod, nome) values (2, 'Romenia')
insert into @teste (cod, nome) values (1, 'Brasil')
insert into @teste (cod, nome) values (1, 'Brasil')
insert into @teste (cod, nome) values (3, 'USA')
insert into @teste (cod, nome) values (4, 'Italia')
insert into @teste (cod, nome) values (3, 'USA')
insert into @teste (cod, nome) values (1, 'Brasil')
insert into @teste (cod, nome) values (3, 'USA')
insert into @teste (cod, nome) values (5, 'Argentina')
insert into @teste (cod, nome) values (2, 'Romenia')

SELECT distinct t.cod, t.nome INTO #tempx 
from  (

SELECT 
    cod,nome, count(*) as qtd
    From @teste
    group by cod,nome
    having COUNT(*)> 1

) T

delete from @teste
where cod in (
    select cod from #tempx
)

INSERT INTO @teste 
SELECT * FROM #tempx

SELECT * FROM @teste