Select the line with the oldest date within that query between the repeated values

Question

Select the line with the oldest date within that query between the repeated values

Navigation

#1 by (0 votes)

0

select e.nm_equipto as EQUIPAMENTOS
    ,Max(remessa.dt_uso_inicio) as DATA_REMESSA --,(SELECT MAX(DT_ENTORC_OFICINA) FROM ORCOS)as DATA_ABERTURA 
    ,MIN(dt_entorc_oficina) as DATA_ABERTURA 
    ,o.numero as NÚMERO 
    ,patr.nr_patrimonio as PATRIMÔNIO 
    ,DATEDIFF ( DAY , MAX(remessa.dt_uso_inicio), o.dt_entorc_oficina)as QTD_DIAS
from orcos as o
inner join equipto as e on o.cd_equipto = e.cd_equipto
inner join patrimon as patr on o.cd_patrimonio = patr.cd_patrimonio
inner join est_almox as almox on almox.cd_almox = patr.cd_almox
inner join v_remessa_patrimonio as remessa on remessa.cd_patrimonio = patr.cd_patrimonio
group by o.numero
    ,patr.nr_patrimonio
    ,o.dt_entorc_oficina
    ,patr.cd_almox
    ,e.nm_equipto
having o.dt_entorc_oficina > max(remessa.dt_uso_inicio)
    and count(o.numero) > 1
    and patr.cd_almox = '6'
order by o.numero desc

! [The data is in this table 1]

sql-server

asked by anonymous 23.03.2017 / 18:13

1 answer

Selenium test stopped JavaScript date conversion issues

score 0 · Accepted Answer

Considering the complete code that you posted in this topic, here is another suggestion:

-- código #2 v5
with tbOPR as (
SELECT r.dt_uso_inicio as DATA_REMESSA,
       o.dt_entorc_oficina as DATA_ABERTURA,
       o.numero as NÚMERO,
       o.cd_equipto,
       p.nr_patrimonio as PATRIMÔNIO, 
       p.cd_almox,
       Seq= row_number() over (partition by p.nr_patrimonio,
                                            r.dt_uso_inicio
                               order by o.dt_entorc_oficina asc)
  from orcos as o
       inner join patrimon as p on o.cd_patrimonio = p.cd_patrimonio
       inner join v_remessa_patrimonio as r on r.cd_patrimonio = p.cd_patrimonio
  where r.dt_uso_inicio < o.dt_entorc_oficina
        and p.cd_almox = '6'
)
SELECT OPR.cd_almox,
       e.nm_equipto as EQUIPAMENTOS,
       OPR.DATA_REMESSA, OPR.DATA_ABERTURA, OPR.NÚMERO, OPR.PATRIMÔNIO,
       QTD_DIAS= DateDiff(day, OPR.DATA_REMESSA, OPR.DATA_ABERTURA)
  from tbOPR as OPR
       inner join equipto as e on OPR.cd_equipto = e.cd_equipto
  where OPR.Seq = 1;