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 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
|
SELECT
GRP,
GROUPE,
CLASSE_ACTION,
CODE_ACTION,
ANNEE,
semaine,
Count(*)
FROM
(SELECT
To_Number(To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 14,'YYYY')) ANNEE,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 14,'WW') semaine,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 14,'YYYYWW') GRP
FROM
DUAL
UNION
SELECT
To_Number(To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 21,'YYYY')) ANNEE,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 21,'WW') semaine,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 21,'YYYYWW') GRP
FROM
DUAL
UNION
SELECT
To_Number(To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 28,'YYYY')) ANNEE,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 28,'WW') semaine,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 28,'YYYYWW')
FROM
DUAL
UNION
SELECT
To_Number(To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 35,'YYYY')) ANNEE,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 35,'WW') semaine,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 35,'YYYYWW')
FROM
DUAL
UNION
SELECT
To_Number(To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 42,'YYYY')) ANNEE,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 42,'WW') semaine,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 42,'YYYYWW') GRP
FROM
DUAL
UNION
SELECT
To_Number(To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 49,'YYYY')) ANNEE,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 49,'WW') semaine,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 49,'YYYYWW') GRP
FROM
DUAL
UNION
SELECT
To_Number(To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 56,'YYYY')) ANNEE,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 56,'WW') semaine,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 56,'YYYYWW') GRP
FROM
DUAL
UNION
SELECT
To_Number(To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 63,'YYYY')) ANNEE,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 63,'WW') semaine,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 63,'YYYYWW') GRP
FROM
DUAL
UNION
SELECT
To_Number(To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 70,'YYYY')) ANNEE,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 70,'WW') semaine,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 70,'YYYYWW') GRP
FROM
DUAL
UNION
SELECT
To_Number(To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 77,'YYYY')) ANNEE,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 77,'WW') semaine,
To_Char(Trunc(NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('02-01-2012', 'DD-MM-YYYY'),'DAY')) ) - 77,'YYYYWW') GRP
FROM
DUAL) Tab1,
ACTIVITE Tab2
WHERE
Tab1.GRP = TO_CHAR(Tab2.DATE,'YYYYWW')(+)
GROUP BY
GROUPE, CLASSE_ACTION, CODE_ACTION, GRP, ANNEE, semaine
ORDER BY
Tab1.GRP ; |
Partager