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 40
| WITH TS AS (
SELECT 'ANI' AS Code, 'Non Importée' AS LIB FROM DUAL UNION ALL
SELECT 'AEC' AS Code, 'En Cours' AS LIB FROM DUAL UNION ALL
SELECT 'AVC' AS Code, 'Validé et confirmé' AS LIB FROM DUAL
),
TR AS (
SELECT 125 AS Num FROM DUAL UNION ALL
SELECT 159 AS Num FROM DUAL UNION ALL
SELECT 180 AS Num FROM DUAL UNION ALL
SELECT 201 AS Num FROM DUAL
),
TR_D_TS AS (
SELECT 125 AS Num,'ANI' AS Code, SYSDATE AS DT_HIST FROM DUAL UNION ALL
SELECT 125 AS Num,'AEC' AS Code, SYSDATE +1 AS DT_HIST FROM DUAL UNION ALL
SELECT 125 AS Num,'AVC' AS Code, SYSDATE +2 AS DT_HIST FROM DUAL UNION ALL
SELECT 159 AS Num,'ANI' AS Code, SYSDATE AS DT_HIST FROM DUAL UNION ALL
SELECT 180 AS Num,'ANI' AS Code, SYSDATE AS DT_HIST FROM DUAL UNION ALL
SELECT 180 AS Num,'AVC' AS Code, SYSDATE +2 AS DT_HIST FROM DUAL UNION ALL
SELECT 201 AS Num,'ANI' AS Code, SYSDATE AS DT_HIST FROM DUAL UNION ALL
SELECT 201 AS Num,'AEC' AS Code, SYSDATE +1 AS DT_HIST FROM DUAL
)
SELECT TT2.NUM
, MAX(TT2.ANI) AS ANI
, MAX(TT2.AEC) AS AEC
, MAX(TT2.AVC) AS AVC
FROM (
SELECT TT.NUM,
CASE WHEN CODE = 'ANI' THEN DT_HIST END ANI,
CASE WHEN CODE = 'AEC' THEN DT_HIST END AEC,
CASE WHEN CODE = 'AVC' THEN DT_HIST END AVC
FROM (
SELECT TR.NUM,
TS.CODE,
TR_D_TS.DT_HIST
FROM TR CROSS JOIN TS
LEFT JOIN TR_D_TS ON TR.NUM = TR_D_TS.NUM
AND TS.CODE = TR_D_TS.CODE
) TT
) TT2
GROUP BY TT2.NUM |
Partager