last_value(eff)
over (partition by siret,date_ca order by
decode(eff,null,to_date(null),datenq) nulls first rows between
unbounded preceding and unbounded following) eff, last_value(eff)
over (partition by siret,date_ca order by
decode(eff,null,to_date(null),datenq) nulls first rows between
unbounded preceding and unbounded following) effnm1 from (select
cplmt.siret, cplmt.date_ca ,cplmt.datenq, base.CA ,base.CA_POND
,base.DATEREP ,base.EFF ,base.EFF_POND ,base.PERIM ,base.TA
,base.UNITE from CONJV200.IV_CAUNQ_E base, ((select
IV_CAUNQ_E.siret, IV_CAUNQ_E.date_ca, IV_CAUNQ_E.datenq from
CONJV200.IV_CAUNQ_E IV_CAUNQ_E, CONJV200.routunq unq where
IV_CAUNQ_E.siret=unq.siret and unq.codenq='INVT' and
unq.datenq=to_date('07/2009','mm/yyyy') and unq.ugst>='01' and
unq.ugst<='01' and (IV_CAUNQ_E.datenq<=to_date('07/2009','mm/yyyy')
and date_ca<=to_date('012008','mmyyyy'))) union (select
IV_CAUNQ_E.siret, to_date('012008','mmyyyy') date_ca,
to_date('07/2009','mm/yyyy') datenq from CONJV200.IV_CAUNQ_E
IV_CAUNQ_E, CONJV200.routunq unq where IV_CAUNQ_E.siret=unq.siret
and unq.codenq='INVT' and unq.datenq=to_date('07/2009','mm/yyyy')
and unq.ugst>='01' and unq.ugst<='01' and not exists (select
bis.siret from CONJV200.IV_CAUNQ_E bis where
(IV_CAUNQ_E.datenq=to_date('07/2009','mm/yyyy') and
date_ca=to_date('012008','mmyyyy')) and
IV_CAUNQ_E.siret=bis.siret))) cplmt where base.siret(+)=cplmt.siret
and base.date_ca(+)=cplmt.date_ca and base.datenq(+)=cplmt.datenq)
CAEFQ) where (datenq=to_date('07/2009','mm/yyyy') and
date_ca=to_date('012008','mmyyyy'))
Partager