Bonjour,
Dans la requête ci-dessous, je ne parviens pas à obtenir la valeur max sur le regroupement mensuel.
Je souhaiterais avoir la dernière valeur pour chaque mois sur la donnée "dernierevaleur"
Merci pour votre aide.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
WITH T AS (SELECT Extract(YEAR  FROM HOPHABS.DAT) AS ANNEE,
                  Extract(MONTH FROM HOPHABS.DAT) AS MOIS,HOPHABS.DAT,
dernierevaleur/60 as dernierevaleur
 
                  CASE 
when to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french')= 1 AND (hophjoun.TYPJOU != 'F')then 'LUN' 
when to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french')= 2 AND (hophjoun.TYPJOU != 'F')then 'MAR' 
when to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french')= 3 AND (hophjoun.TYPJOU != 'F')then 'MER' 
when to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french')= 4 AND (hophjoun.TYPJOU != 'F')then 'JEU' 
when to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french')= 5 AND (hophjoun.TYPJOU != 'F')then 'VEN' 
when to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french')= 6 AND (hophjoun.TYPJOU != 'F')then 'SAM' 
when to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french')= 7 AND (hophjoun.TYPJOU != 'F')then 'DIM' 
Else 'FERIE'
END AS NUMJOUR,
HOPEMPL.NOMPRE as NOM,
CASE
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('1')) and  HOPHJOUN.TYPJOU != 'F') and HOPHABS.MOTIF = ('SPOCDG24J') THEN 1 
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('1')) and  HOPHJOUN.TYPJOU != 'F') and HOPHABS.MOTIF in ('SPOCDG12J','SPOCDG12N')THEN 0.5
else 0
end as NBLUN ,
CASE
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('2')) and  HOPHJOUN.TYPJOU != 'F') and HOPHABS.MOTIF = ('SPOCDG24J') THEN 1 
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('2')) and  HOPHJOUN.TYPJOU != 'F') and HOPHABS.MOTIF in ('SPOCDG12J','SPOCDG12N')THEN 0.5
else 0
end as NBMAR ,
CASE
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('3')) and  HOPHJOUN.TYPJOU != 'F') and HOPHABS.MOTIF = ('SPOCDG24J') THEN 1 
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('3')) and  HOPHJOUN.TYPJOU != 'F') and HOPHABS.MOTIF in ('SPOCDG12J','SPOCDG12N')THEN 0.5
else 0
end as NBMER ,
CASE
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('4')) and  HOPHJOUN.TYPJOU != 'F') and HOPHABS.MOTIF = ('SPOCDG24J') THEN 1 
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('4')) and  HOPHJOUN.TYPJOU != 'F') and HOPHABS.MOTIF in ('SPOCDG12J','SPOCDG12N')THEN 0.5
else 0
end as NBJEU ,
CASE
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('5')) and  HOPHJOUN.TYPJOU != 'F' and HOPHABS.MOTIF in ('SPOCDG24J')) THEN 1 
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('5')) and  HOPHJOUN.TYPJOU != 'F' and HOPHABS.MOTIF in ('SPOCDG12J','SPOCDG12N')) THEN 0.5
else 0
end as NBVEN ,
CASE
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('6')) and HOPHABS.MOTIF in ('SPOCDG24J')) THEN 1 
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('6'))  and HOPHABS.MOTIF in ('SPOCDG12J','SPOCDG12N')) THEN 0.5
else 0
end as NBSAM ,
CASE
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('7'))  and HOPHABS.MOTIF in ('SPOCDG24J')) THEN 1 
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') in('7'))  and HOPHABS.MOTIF in ('SPOCDG12J','SPOCDG12N')) THEN 0.5
else 0
end as NBDIM,
CASE
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') not in('7','6') and  HOPHJOUN.TYPJOU = 'F')  and HOPHABS.MOTIF in ('SPOCDG24J')) THEN 1 
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french') not in('7','6') and  HOPHJOUN.TYPJOU = 'F') and HOPHABS.MOTIF in ('SPOCDG12J','SPOCDG12N')) THEN 0.5
else 0
end as NBFERIE,
CASE
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french')  in('1','2','3','4','5','7','6'))  and HOPHABS.MOTIF in ('SPOCDG24J')) THEN 1 
WHEN ((to_char(hophabs.dat, 'D', 'NLS_DATE_LANGUAGE=french')  in('1','2','3','4','5','7','6')) and HOPHABS.MOTIF in ('SPOCDG12J','SPOCDG12N')) THEN 0.5
else 0
end as NBTOTG
 
 
          FROM HOPEMPL, HOPHABS, HOPHJOUN,HOPHJOUP
WHERE HOPEMPL.MATRI = HOPHABS.MATRI
AND HOPHJOUP.MATRI = HOPEMPL.MATRI
AND HOPHJOUP.MATRI = HOPHJOUN.MATRI
AND HOPHJOUP.DAT = HOPHJOUN.DAT
AND HOPHJOUP.MATRI = HOPHABS.MATRI
AND HOPHJOUP.DAT = HOPHABS.DAT
AND HOPEMPL.MATRI = HOPHJOUN.MATRI
AND HOPHABS.MATRI = HOPHJOUN.MATRI
AND HOPHABS.DAT = HOPHJOUN.DAT
AND HOPEMPL.SEITYPPOP in ('V','R')
AND HOPHABS.MOTIF like 'SPOCDG%'
/*AND HOPHABS.MOTIF = 'SPODISPOAS'*/
AND EXTRACT(YEAR FROM HOPHABS.DAT) = '2020' /*AND EXTRACT(MONTH FROM HOPHABS.DAT) in( '05')*/
)
SELECT ANNEE, MOIS,NOM,SUM(NBLUN) as NBLUN,SUM(NBMAR) as NBMAR,SUM(NBMER) as NBMER,SUM(NBJEU) as NBJEU,SUM(NBVEN) as NBVEN,SUM(NBSAM) as NBSAM, SUM(NBDIM) as NBDIM, SUM(NBFERIE) as NBFERIE, SUM(NBTOTG) as NBTOTG,MAX(dernierevaleur) as dernierevaleur
FROM T
GROUP BY NOM,ANNEE , MOIS,dernierevaleur
ORDER BY ANNEE/*,NBTOTG desc*/, NOM  , MOIS