1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| WITH t AS (SELECT TO_DATE('31/12/2011', 'DD/MM/YYYY') dateinv, 'AAA' code, 5 nombre FROM dual
UNION ALL SELECT TO_DATE('31/10/2010', 'DD/MM/YYYY') dateinv, 'AAA' code, 1 nombre FROM dual
UNION ALL SELECT TO_DATE('31/12/2011', 'DD/MM/YYYY') dateinv, 'AAA' code, 3 nombre FROM dual
UNION ALL SELECT TO_DATE('31/10/2010', 'DD/MM/YYYY') dateinv, 'AAA' code, 4.5 nombre FROM dual
UNION ALL SELECT TO_DATE('31/12/2011', 'DD/MM/YYYY') dateinv, 'BBB' code, 5 nombre FROM dual
UNION ALL SELECT TO_DATE('05/09/2011', 'DD/MM/YYYY') dateinv, 'BBB' code, 2 nombre FROM dual
UNION ALL SELECT TO_DATE('05/09/2011', 'DD/MM/YYYY') dateinv, 'BBB' code, 8.5 nombre FROM dual
UNION ALL SELECT TO_DATE('31/12/2011', 'DD/MM/YYYY') dateinv, 'BBB' code, 4 nombre FROM dual
UNION ALL SELECT TO_DATE('31/12/2012', 'DD/MM/YYYY') dateinv, 'AAA' code, 5 nombre FROM dual
UNION ALL SELECT TO_DATE('31/10/2012', 'DD/MM/YYYY') dateinv, 'AAA' code, 1 nombre FROM dual
UNION ALL SELECT TO_DATE('31/12/2012', 'DD/MM/YYYY') dateinv, 'AAA' code, 3 nombre FROM dual
UNION ALL SELECT TO_DATE('30/11/2011', 'DD/MM/YYYY') dateinv, 'CCC' code, 3 nombre FROM dual)
SELECT dateinv, code, SUM(nombre)
FROM t a
WHERE dateinv = (SELECT MAX(dateinv) FROM t b WHERE b.code = a.code AND b.dateinv <= TO_DATE('31.12.2011', 'DD.MM.YYYY'))
GROUP BY dateinv, code
DATEINV CODE SUM(NOMBRE)
30/11/2011 CCC 3
31/12/2011 AAA 8
31/12/2011 BBB 9 |