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
|
WITH Courses AS (
SELECT TO_DATE('22/01/2011','DD/MM/RRRR') Dt, 8 Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('29/01/2011','DD/MM/RRRR') Dt, 2 Place, 4 NumCourse FROM dual
UNION ALL SELECT TO_DATE('30/01/2011','DD/MM/RRRR') Dt, 7 Place, 3 NumCourse FROM dual
UNION ALL SELECT TO_DATE('30/01/2011','DD/MM/RRRR') Dt, 5 Place, 5 NumCourse FROM dual
UNION ALL SELECT TO_DATE('30/01/2011','DD/MM/RRRR') Dt, 2 Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('31/01/2011','DD/MM/RRRR') Dt, 3 Place, 1 NumCourse FROM dual
UNION ALL SELECT TO_DATE('05/02/2011','DD/MM/RRRR') Dt, 1 Place, 4 NumCourse FROM dual
UNION ALL SELECT TO_DATE('06/02/2011','DD/MM/RRRR') Dt, 6 Place, 5 NumCourse FROM dual
UNION ALL SELECT TO_DATE('07/02/2011','DD/MM/RRRR') Dt, 5 Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('07/02/2011','DD/MM/RRRR') Dt, 8 Place, 8 NumCourse FROM dual
UNION ALL SELECT TO_DATE('10/02/2011','DD/MM/RRRR') Dt, 6 Place, 3 NumCourse FROM dual
UNION ALL SELECT TO_DATE('12/02/2011','DD/MM/RRRR') Dt, 3 Place, 2 NumCourse FROM dual
UNION ALL SELECT TO_DATE('13/02/2011','DD/MM/RRRR') Dt, 3 Place, 4 NumCourse FROM dual
UNION ALL SELECT TO_DATE('13/02/2011','DD/MM/RRRR') Dt, 2 Place, 5 NumCourse FROM dual
UNION ALL SELECT TO_DATE('14/02/2011','DD/MM/RRRR') Dt, 2 Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('20/02/2011','DD/MM/RRRR') Dt, 9 Place, 1 NumCourse FROM dual
UNION ALL SELECT TO_DATE('20/02/2011','DD/MM/RRRR') Dt, 2 Place, 4 NumCourse FROM dual
UNION ALL SELECT TO_DATE('20/02/2011','DD/MM/RRRR') Dt, 6 Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('21/02/2011','DD/MM/RRRR') Dt, -1 Place, 6 NumCourse FROM dual
UNION ALL SELECT TO_DATE('22/02/2011','DD/MM/RRRR') Dt, 3 Place, 5 NumCourse FROM dual
UNION ALL SELECT TO_DATE('23/02/2011','DD/MM/RRRR') Dt, 2 Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('23/02/2011','DD/MM/RRRR') Dt, 3 Place, 8 NumCourse FROM dual
UNION ALL SELECT TO_DATE('24/02/2011','DD/MM/RRRR') Dt, 4 Place, 2 NumCourse FROM dual
UNION ALL SELECT TO_DATE('24/02/2011','DD/MM/RRRR') Dt, -1 Place, 4 NumCourse FROM dual
UNION ALL SELECT TO_DATE('25/02/2011','DD/MM/RRRR') Dt, 1 Place, 5 NumCourse FROM dual
UNION ALL SELECT TO_DATE('03/03/2011','DD/MM/RRRR') Dt, 5 Place, 1 NumCourse FROM dual
UNION ALL SELECT TO_DATE('05/03/2011','DD/MM/RRRR') Dt, 6 Place, 2 NumCourse FROM dual
UNION ALL SELECT TO_DATE('06/03/2011','DD/MM/RRRR') Dt, 4 Place, 1 NumCourse FROM dual
UNION ALL SELECT TO_DATE('13/03/2011','DD/MM/RRRR') Dt, 9 Place, 6 NumCourse FROM dual
),
w AS (
SELECT dt, place, numcourse, DENSE_RANK() OVER(ORDER BY dt) DENSE_RNK,
CASE WHEN place BETWEEN 1 AND 3 THEN 1 ELSE 0 END gagne
FROM Courses
)
SELECT a.dt, a.numcourse, a.place, a.dense_rnk, a.gagne,
NVL((SELECT 'Yes' FROM w b WHERE b.dense_rnk BETWEEN a.dense_rnk -3 AND a.dense_rnk - 1 HAVING SUM(gagne) >= 3), 'No') AS Play
FROM w a
ORDER BY dt, numcourse |