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
|
SELECT code_langage, CASE
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-1),'MM/YYYY') THEN 'A'
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-2),'MM/YYYY') THEN 'B'
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-3),'MM/YYYY') THEN 'C'
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-4),'MM/YYYY') THEN 'D'
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-5),'MM/YYYY') THEN 'E'
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-6),'MM/YYYY') THEN 'F'
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-7),'MM/YYYY') THEN 'G'
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-8),'MM/YYYY') THEN 'H'
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-9),'MM/YYYY') THEN 'I'
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-10),'MM/YYYY') THEN 'J'
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-11),'MM/YYYY') THEN 'K'
WHEN SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) like to_char(add_months(sysdate,-12),'MM/YYYY') THEN 'L'
ELSE 'x'
END AS MOIS, count(*)
FROM BIG_DOUL, BIG_ALPATRES, BIG_LITRES
WHERE BIG_ALPATRES.Id_ALPATRES=BIG_LITRES.Id_ALPATRES
AND BIG_LITRES.id_DOUL=BIG_DOUL.id_DOUL
AND code_langage = 'SQL'
AND BIG_ALPATRES.ALPATRES like 'OPTRE0065'
AND SUBSTR(to_char(date_creation,'DD/MM/YYYY'),3,4)IS NOT NULL
AND SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7) IN
(to_char(add_months(sysdate,-1),'MM/YYYY'),
to_char(add_months(sysdate,-2),'MM/YYYY'),
to_char(add_months(sysdate,-3),'MM/YYYY'),
to_char(add_months(sysdate,-4),'MM/YYYY'),
to_char(add_months(sysdate,-5),'MM/YYYY'),
to_char(add_months(sysdate,-6),'MM/YYYY'),
to_char(add_months(sysdate,-7),'MM/YYYY'),
to_char(add_months(sysdate,-8),'MM/YYYY'),
to_char(add_months(sysdate,-9),'MM/YYYY'),
to_char(add_months(sysdate,-10),'MM/YYYY'),
to_char(add_months(sysdate,-11),'MM/YYYY'),
to_char(add_months(sysdate,-12),'MM/YYYY'))
GROUP BY code_langage, SUBSTR(to_char(date_creation,'DD/MM/YYYY'),4,7)
ORDER BY MOIS DESC, code_langage |
Partager