Pesquisar na Comunidade
Mostrando resultados para as tags ''linhas redundantes''.
Encontrado 1 registro
-
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