1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
|
SELECT X1.IDCPTEX1
, X1.ANNCPTEX1
, CASE WHEN ??.ANNCPTE = 2
THEN ABS(B1GRTEAP) * -1
ELSE B1.GRTEAPB1
END
, CASE WHEN ??.ANNCPTE = 2
THEN ABS(??.B1GRTEAP) * -1
ELSE B1.GRTEAPB1
END
, CASE WHEN ??.ANNCPTE = 2
THEN ABS(??.B1VLBEAP) * -1
ELSE B1.VLBEAPB1
END
, SUB1.COL1 as Dernier_IDCPTEX1
, SUB1.COL2 as Dernier_VLBEAPB1
, SUB1.COL3 as Dernier_DTETRDB1
, SUB1.COL4 as Dernier_ORDPTRB1
FROM BDDX1 X1
JOIN BDDB1 B1
on B1.CPTEB1 = X1.CPTEX1
join (SELECT IDCPTEX1 COL1
, VLBEAPB1 COL2
, DTETRDB1 COL3
, ORDPTRB1 COL4
, CPTEX1 COL5
FROM BDDX1
JOIN BDDB1
on CPTEB1 = CPTEX1
where not exists
(select 1
from BDDB1 SUB2
where SUB2.CPTEX1 =X1.CPTEX1
and SUB2.DTETRDB1>X1.DTETRDB1) -- filtre date la plus récente ici !
) as SUB1
on SUB1.COL5=X1.CPTEX1
order by B1.ORDPTRB1 desc |
Partager