1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| SELECT EXTRACT (YEAR FROM tb1.dat_serv) AS annee
, EXTRACT (MONTH FROM tb1.dat_serv) AS mois
, tb1.date
, tb1.no
, tb1.cla
, tb1.disp
, ROW_NUMBER() OVER(PARTITION BY annee, mois, tb1.cla, tb1.disp ORDER BY tb1.date, tb1.no) AS rang
, tb2.nam
, COUNT(*) OVER (PARTITION BY tb1.cla, tb1.disp, mois) AS cpt
FROM table AS tbl
INNER JOIN
table2 AS tb2
ON tb2.no_indiv = tb1.no_indiv
AND tb2.cod_sta = 'VIG'
WHERE tb1.date BETWEEN '2013-01-01' AND '2013-12-31'
QUALIFY rang > 6
AND cpt > 6
ORDER BY tb1.date
, tb1.no
; |
Partager