Eliminar linhas redundantes
Bom dia pessoAll,
Tenho uma query que retorna o seguinte resultado:
/applications/core/interface/imageproxy/imageproxy.php?img=https://social.msdn.microsoft.com/Forums/getfile/1439390&key=fded6911b5254eded977ca60afbe3609c100ce46a7ea53e232a11bad3612ab7e" data-imageproxy-source="https://social.msdn.microsoft.com/Forums/getfile/1439390" />
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 CoutinhoDiscussão (2)
Carregando comentários...