I'm making the difference between dates, when the designacao_circuito
column is the same, and the difference (days) between dt_hr_fecom
is < = 10, I need it in Reinc_10_Dias
column to write Reincidente =<10 dias
, but not first record and not the second.
I made the following command:
select
BASE_RREIP.designacao_circuito,
BASE_RREIP.num_rec,
BASE_RREIP.dt_abertura_rec,
BASE_RREIP.dt_hr_abertura,
BASE_RREIP.[dt_rec_fechamento_tecnico],
BASE_RREIP.[dt_hr_fetec],
BASE_RREIP.dt_hr_fecom,
BASE_RREIP.rreip,
UDS.[GERENCIA OPERACIONAL SUB] AS GERENCIATECNICASUB,
BASE_RREIP.centro_funcional_local_anor,
BASE_RREIP.UDS_Ofensor,
BASE_RREIP.Piramide,
BASE_RREIP.Tipo_RECs,
BASE_RREIP.nome_causa_anor_rec,
BASE_RREIP.nome_guerra,
ROW_NUMBER() OVER(PARTITION BY designacao_circuito ORDER BY designacao_circuito,dt_hr_fecom asc) as qtde, ----- Numerando a quantidade vezes que se repete a Designação do Circuito
(designacao_circuito + '-' + cast(row_number() over (partition by designacao_circuito order by dt_hr_abertura) as varchar)) as circuito_RREIP, ---- CONCAT da Designação com o Campo acima
case when datediff(day,lag(dt_hr_fecom) over (partition by designacao_circuito order by dt_hr_fecom asc),dt_hr_fecom) <= 10
then 'Reincidente =<10 dias' else 'Ñ reincidente =<10 dias' end as 'Reinc_10_Dias', ----- INFORMA SE É REINCIDENTE OU NÃO
MONTH(dt_hr_abertura) as mês_ABERT,
MONTH(dt_hr_fecom) as mês_FECOM
from BASE_RREIP LEFT JOIN UDS
ON BASE_RREIP.UDS_Ofensor = UDS.UDS
The result was:
Otherthantheaboveprint,theinformation"Repeated =