Discussion :

[joponta]

Bonjour,
Dans la requête ci-dessous je sors une ligne par matricule.
Je souhaiterais en plus sortir une colonne de plus avec l'information d'une donnée à une date précise ( en l'occurence le 31/08 dans l'exemple).
J'ai essayé avec un case when dans la requête, mais il sort cette fois deux lignespar matricule.
Comment faire pour avoir cette information sur la même ligne ?
Le code :

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
82
SELECT 
case 
when HOPEMPL.MATRI in('003252','005719','006370','008383','007733','006947','006400','006776','005468','006215','006537','000132','006714','008782') then 'HDR'  
when HOPEMPL.MATRI in('000130','003037','006684','005623','005891','006189') then 'ADJ'
when HOPEMPL.MATRI in('008381','006212','006975','006235','005465','004512','006214','005234','003243','006216','005493','003244','005825') then 'POOL'
when HOPEMPL.MATRI in('005638','005741','008382','005201','005798','804001','003436','004899','004723','000099','005842') then 'CA1EQ'
ELSE 'AUTRE'
end as POPULATION,
HOPEMPL.MATRI,
HOPEMPl.NOMPRE,round(SUM(HOPHJOUP.SJQTPRESJ/60),2)  as SUMPRES, round(max(HOPHJOuP.SJRHG12SD/60) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc),2) AS SDG12,
round(max(HOPHJOUP.SJRHG24SD/60/17.1*24) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc),2) AS SDG24,
round(max(HOPHJOUP.SJRHG24SD/60) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc),2) AS SDWG24,
round(max(HOPHJOuP.SJRHG12SD/60/12) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc),2) +
max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60/12*0.2376) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc) as NBG12,
round(max(HOPHJOUP.SJRHG24SD/60/17.1) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc),2)+
round(max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60/17.1*0.7624) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc),2)AS NBG24,
round((round(max(HOPHJOUP.SJRHG24SD/60/17.1) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc),2)+
round(max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60/17.1*0.7624) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc),2))/4,1)  AS NBG24MOIS,
 
round((round(max(HOPHJOuP.SJRHG12SD/60/12) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc),2) +
max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60/12*0.2376) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc))/4,1) as NBG12MOIS,
max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc) AS SDPHS,
 
round(max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60/17.1*24*0.7624) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc),2) AS SDPHSG24,
 
max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60*0.2376) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc) AS SDPHSG12,
 
round(SUM(HOPHJOUP.SJQTPRESJ/60) + max(HOPHJOuP.SJRHG12SD/60) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc)+
 
max(HOPHJOUP.SJRHG24SD/60/17.1*24) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc)+
 
max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60/17.1*24*0.7624) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc)+
 
max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60*0.2376) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc),2) AS PREVIPRESENCE,
 
round(SUM(HOPHJOUP.SJQTPRESJ/60) + max(HOPHJOuP.SJRHG12SD/60) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc)+
 
max(HOPHJOUP.SJRHG24SD/60/17.1*24) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc)+
 
max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60/17.1*24*0.7624) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc)+
 
max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60*0.2376) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc) - 2256,2) AS PREVIPRESENCE,
 
max(HOPHJOuP.IJRBHSEXOA/60) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc) AS HSPREV,
 
max(HOPHJOUP.SJRROPEG/60) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc) AS HSPAYE,
 
max((HOPHJOuP.IJRBHSEXOA - HOPHJOUP.SJRROPEG)/60) keep (dense_rank first ORDER BY HOPHJOUP.DAT desc) AS SOLDEHS,
 
 
(Case when HOPHJOUP.DAT = '31/08/2020' then HOPHJOUP.SJRTPS3/60 End) as TIODEB
 
 
From HOPEMPL,HOPHJOUN, HOPHJOUP
 
 
WHERE HOPHJOUP.DAT = HOPHJOUN.DAT
 
AND HOPHJOUP.MATRI = HOPHJOUN.MATRI
 
AND HOPHJOUP.MATRI = HOPEMPl.MATRI
 
AND HOPHJOUN.SECTORI = '012234'
 
AND HOPHJOUP.MATRI = '003252'
 
AND HOPHJOUP.CTTYPPOP in ('P', 'W','X','Y')
 
AND EXTRACT ( YEAR FROM HOPHJOUP.DAT) = 2020
 
GROUP BY HOPEMPL.NOMPRE, HOPEMPL.MATRI,
case 
when HOPEMPL.MATRI in('003252','005719','006370','008383','007733','006947','006400','006776','005468','006215','006537','000132','006714','008782') then 'HDR'  
when HOPEMPL.MATRI in('000130','003037','006684','005623','005891','006189') then 'ADJ'
when HOPEMPL.MATRI in('008381','006212','006975','006235','005465','004512','006214','005234','003243','006216','005493','003244','005825') then 'POOL'
when HOPEMPL.MATRI in('005638','005741','008382','OO5201','005798','804001','003436','004899','004723','000099','005842') then 'CA1EQ'
ELSE 'AUTRE'
end,
 
Case when HOPHJOUP.DAT = '31/08/2020' then HOPHJOUP.SJRTPS3/60 End
 
ORDER BY HOPEMPL.NOMPRE

La ligne de code avec le case when

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
(Case when HOPHJOUP.DAT = '31/08/2020' then HOPHJOUP.SJRTPS3/60 End) as TIODEB

[vanagreg]

Bonjour,

Applique un MAX sur cette ligne (pas besoin qu'elle soit dans le GROUP BY donc):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MAX(Case when HOPHJOUP.DAT = '31/08/2020' then HOPHJOUP.SJRTPS3/60 End) as TIODEB

[joponta]

Ok merci à toi ça fonctionne.