Maior data

[tiagoof]

Olá pessoal,

Preciso trazer a maior data do campo ENT.ENT_DT_ENTREGA no código abaixo, pois dentro do sistema geralmente são cadastradas três datas, sendo que para cada registro devo trazer apenas a maior.

Selecionar tudo

select EST.EST_ST_CODIGO,
       BL.EMP_EST_IN_CODIGO,
       EST.EST_ST_NOME,
       EST.CUS_IN_REDUZIDO,
       EST.CUSE_IN_REDUZIDO,
       EST.PRO_IN_REDUZIDO,
       EST.EST_DT_LANCAMENTO,
       BL.BLO_IN_NUMUNIDADES,
       BL.BLO_IN_NUMANDARES,
       ENT.ENT_DT_ENTREGA,                     
       EST.EST_DT_HABITE
from MGDBM.DBM_ESTRUTURA EST, MGDBM.DBM_BLOCO BL, MGDBM.DBM_ENTREGA_OBRA ENT
where EST.EST_CH_TIPOESTRUTURA = 'B' AND
      EST.ORG_TAB_IN_CODIGO = BL.ORG_TAB_IN_CODIGO    AND
      EST.ORG_PAD_IN_CODIGO = BL.ORG_PAD_IN_CODIGO    AND
      EST.ORG_IN_CODIGO     = BL.ORG_IN_CODIGO        AND
      EST.ORG_TAU_ST_CODIGO = BL.ORG_TAU_ST_CODIGO    AND
      EST.EST_IN_CODIGO     = BL.EST_IN_CODIGO        AND
      EST.ORG_TAB_IN_CODIGO = ENT.ORG_TAB_IN_CODIGO    AND
      EST.ORG_PAD_IN_CODIGO = ENT.ORG_PAD_IN_CODIGO    AND
      EST.ORG_IN_CODIGO     = ENT.ORG_IN_CODIGO        AND
      EST.ORG_TAU_ST_CODIGO = ENT.ORG_TAU_ST_CODIGO    AND
      EST.EST_IN_CODIGO     = ENT.EST_IN_CODIGO        AND
      EST.FIL_IN_CODIGO not between 36 and 37   AND        
      EST.fil_in_codigo not between 106 and 197 AND
      EST.fil_in_codigo not between 320 and 323 AND
      EST.fil_in_codigo not between 326 and 330 AND
      EST.fil_in_codigo not between 372 and 373 AND
      EST.fil_in_codigo not between 378 and 379 AND
      EST.fil_in_codigo not between 202 and 210 AND
      EST.fil_in_codigo not between 211 and 212 AND
      EST.fil_in_codigo not between 213 and 228 AND
      EST.fil_in_codigo not between 231 and 246 AND
      EST.fil_in_codigo not between 247 and 259 AND          
      EST.fil_in_codigo not between 331 and 332 AND
      EST.fil_in_codigo not between 349 and 356 AND
      EST.fil_in_codigo not between 372 and 373 AND
      EST.fil_in_codigo not between 378 and 379 AND
      EST.fil_in_codigo not between 392 and 399 AND
      EST.fil_in_codigo not between 406 and 409 AND
      EST.fil_in_codigo not between 414 and 444 AND
      EST.fil_in_codigo not between 457 and 458 AND
      EST.fil_in_codigo not between 596 and 599 AND
      EST.fil_in_codigo not between 4862 and 4862;

Vocês conseguem me ajudar com esta encrenca?

Obrigado pela ajuda,
Abs

[Rafael_Santos]

Tente isso:

Selecionar tudo

select EST.EST_ST_CODIGO,
       BL.EMP_EST_IN_CODIGO,
       EST.EST_ST_NOME,
       EST.CUS_IN_REDUZIDO,
       EST.CUSE_IN_REDUZIDO,
       EST.PRO_IN_REDUZIDO,
       EST.EST_DT_LANCAMENTO,
       BL.BLO_IN_NUMUNIDADES,
       BL.BLO_IN_NUMANDARES,
       MAX(ENT.ENT_DT_ENTREGA),                     
       EST.EST_DT_HABITE
from 
       MGDBM.DBM_ESTRUTURA EST 
       JOIN MGDBM.DBM_BLOCO BL USING(EST.ORG_TAB_IN_CODIGO = BL.ORG_TAB_IN_CODIGO    AND
                                     EST.ORG_PAD_IN_CODIGO = BL.ORG_PAD_IN_CODIGO    AND
                                     EST.ORG_IN_CODIGO     = BL.ORG_IN_CODIGO        AND
                                     EST.ORG_TAU_ST_CODIGO = BL.ORG_TAU_ST_CODIGO    AND
                                     EST.EST_IN_CODIGO     = BL.EST_IN_CODIGO) 
       JOIN MGDBM.DBM_ENTREGA_OBRA ENT USING(EST.ORG_TAB_IN_CODIGO = ENT.ORG_TAB_IN_CODIGO    AND
                                             EST.ORG_PAD_IN_CODIGO = ENT.ORG_PAD_IN_CODIGO    AND
                                             EST.ORG_IN_CODIGO     = ENT.ORG_IN_CODIGO        AND
                                             EST.ORG_TAU_ST_CODIGO = ENT.ORG_TAU_ST_CODIGO    AND
                                             EST.EST_IN_CODIGO     = ENT.EST_IN_CODIGO)
where EST.EST_CH_TIPOESTRUTURA = 'B' AND             
      EST.FIL_IN_CODIGO not between 36 and 37   AND        
      EST.fil_in_codigo not between 106 and 197 AND
      EST.fil_in_codigo not between 320 and 323 AND
      EST.fil_in_codigo not between 326 and 330 AND
      EST.fil_in_codigo not between 372 and 373 AND
      EST.fil_in_codigo not between 378 and 379 AND
      EST.fil_in_codigo not between 202 and 210 AND
      EST.fil_in_codigo not between 211 and 212 AND
      EST.fil_in_codigo not between 213 and 228 AND
      EST.fil_in_codigo not between 231 and 246 AND
      EST.fil_in_codigo not between 247 and 259 AND          
      EST.fil_in_codigo not between 331 and 332 AND
      EST.fil_in_codigo not between 349 and 356 AND
      EST.fil_in_codigo not between 372 and 373 AND
      EST.fil_in_codigo not between 378 and 379 AND
      EST.fil_in_codigo not between 392 and 399 AND
      EST.fil_in_codigo not between 406 and 409 AND
      EST.fil_in_codigo not between 414 and 444 AND
      EST.fil_in_codigo not between 457 and 458 AND
      EST.fil_in_codigo not between 596 and 599 AND
      EST.fil_in_codigo not between 4862 and 4862
group by
      EST.EST_ST_CODIGO,
      BL.EMP_EST_IN_CODIGO,
      EST.EST_ST_NOME,
      EST.CUS_IN_REDUZIDO,
      EST.CUSE_IN_REDUZIDO,
      EST.PRO_IN_REDUZIDO,
      EST.EST_DT_LANCAMENTO,
      BL.BLO_IN_NUMUNIDADES,
      BL.BLO_IN_NUMANDARES,       
      EST.EST_DT_HABITE;

modifiquei seu join para facilitar a visualização do seu filtro.