Discussion :

[chtom]

Bonjour,

J'ai une table TEST qui contient ces données :
LOGON_DAY / LOGIN
01/01/2010 08:13:15 / DUPOND
01/01/2010 08:15:40 / DURAND
01/01/2010 09:15:12 / DURAND
02/01/2010 10:12:15 / MARTIN

Actuellement j'ai écrit une requete qui me retourne ceci :

01/01/2010 08:00 2
01/01/2010 09:00 1
01/01/2010 10:00 0
02/01/2010 08:00 0
02/01/2010 09:00 0
02/01/2010 10:00 1


J'aimerais arriver à faire ceci :
08h00 / 09h00 / 10h00
01/01/2010 / 2 / 1 / 0
02/02/2010 / 0 / 0 / 1
03/02/2010 / 0 / 0 / 0
04/02/2010 / 0 / 0 / 0
05/02/2010 / 0 / 0 / 0
06/02/2010 / 0 / 0 / 0
07/02/2010 / 0 / 0 / 0


Merci.

[mongilotti]

c'est la présentation d'une matrice.

[Waldar]

En supposant que vous n'avez pas de table calendrier, voici une solution qui utilise la fonction PIVOT (11g).
J'ai limité à la tranche 6h / 11h pour réduire l'affichage, mais vous pouvez décommenter pour avoir toutes les heures :

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
WITH TT AS
(
select to_date('01/01/2010 08:13:15', 'dd/mm/yyyy hh24:mi:ss') dy, 'DUPOND' lg from dual union all
select to_date('01/01/2010 08:15:40', 'dd/mm/yyyy hh24:mi:ss')   , 'DURAND'    from dual union all
select to_date('01/01/2010 09:15:12', 'dd/mm/yyyy hh24:mi:ss')   , 'DURAND'    from dual union all
select to_date('02/01/2010 10:12:15', 'dd/mm/yyyy hh24:mi:ss')   , 'MARTIN'    from dual
)
  ,  HR AS
(
select level-1 as hrs, to_char(level-1, 'FM00') || 'h00' as hr_rep
  from dual
connect by level <= 24
)
  ,  CL AS
(
select date '2010-01-01' + level -1 as dy
  from dual
connect by level <= 7
)
  ,  SR1 AS
(
  select trunc(dy) as dy,
         to_number(to_char(dy, 'hh24')) as hr,
         count(*) as nb
    from TT
group by trunc(dy), to_number(to_char(dy, 'hh24'))
)
  ,  SR2 AS
(
select CL.dy, HR.hr_rep, coalesce(SR1.nb, 0) as nb
  FROM CL
       cross join HR
       left outer join SR1
         on SR1.dy = CL.dy
        and SR1.hr = HR.hrs
)
select *
  from sr2
pivot (max(nb)
       for hr_rep in (--'00h00', '01h00', '02h00', '03h00', '04h00', '05h00',
                      '06h00', '07h00', '08h00', '09h00', '10h00', '11h00'))
                      --'12h00', '13h00', '14h00', '15h00', '16h00', '17h00',
                      --'18h00', '19h00', '20h00', '21h00', '22h00', '23h00'))
order by dy asc;
 
DY		'06h00'	'07h00'	'08h00'	'09h00'	'10h00'	'11h00'
01/01/2010	0	0	2	1	0	0
02/01/2010	0	0	0	0	1	0
03/01/2010	0	0	0	0	0	0
04/01/2010	0	0	0	0	0	0
05/01/2010	0	0	0	0	0	0
06/01/2010	0	0	0	0	0	0
07/01/2010	0	0	0	0	0	0

Si vous n'êtes pas en 11g :

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
WITH TT AS
(
select to_date('01/01/2010 08:13:15', 'dd/mm/yyyy hh24:mi:ss') dy, 'DUPOND' lg from dual union all
select to_date('01/01/2010 08:15:40', 'dd/mm/yyyy hh24:mi:ss')   , 'DURAND'    from dual union all
select to_date('01/01/2010 09:15:12', 'dd/mm/yyyy hh24:mi:ss')   , 'DURAND'    from dual union all
select to_date('02/01/2010 10:12:15', 'dd/mm/yyyy hh24:mi:ss')   , 'MARTIN'    from dual
)
  ,  HR AS
(
select level-1 as hrs
  from dual
connect by level <= 24
)
  ,  CL AS
(
select date '2010-01-01' + level -1 as dy
  from dual
connect by level <= 7
)
  ,  SR1 AS
(
  select trunc(dy) as dy,
         to_number(to_char(dy, 'hh24')) as hr,
         count(*) as nb
    from TT
group by trunc(dy), to_number(to_char(dy, 'hh24'))
)
  select CL.dy,
         max(case HR.hrs when  6 then coalesce(SR1.nb, 0) end) as "06h00",
         max(case HR.hrs when  7 then coalesce(SR1.nb, 0) end) as "07h00",
         max(case HR.hrs when  8 then coalesce(SR1.nb, 0) end) as "08h00",
         max(case HR.hrs when  9 then coalesce(SR1.nb, 0) end) as "09h00",
         max(case HR.hrs when 10 then coalesce(SR1.nb, 0) end) as "10h00",
         max(case HR.hrs when 11 then coalesce(SR1.nb, 0) end) as "11h00"
    FROM CL
         cross join HR
         left outer join SR1
           on SR1.dy = CL.dy
          and SR1.hr = HR.hrs
GROUP BY CL.dy
ORDER BY CL.dy ASC;
 
DY		06h00	07h00	08h00	09h00	10h00	11h00
01/01/2010	0	0	2	1	0	0
02/01/2010	0	0	0	0	1	0
03/01/2010	0	0	0	0	0	0
04/01/2010	0	0	0	0	0	0
05/01/2010	0	0	0	0	0	0
06/01/2010	0	0	0	0	0	0
07/01/2010	0	0	0	0	0	0

[boutade80]

Autre solution.
Plus couteuse que waldar mais celle ci apporte un calcul automatique des bornes sur les dates

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
with req as
(
select to_date('01/01/2010 08:13:15','DD/MM/YYYY HH24:MI:SS') as LOGON_DAY,'DUPOND' as LOGIN from dual union all
select to_date('01/01/2010 08:15:40','DD/MM/YYYY HH24:MI:SS') as LOGON_DAY,'DURAND' as LOGIN from dual union all
select to_date('01/01/2010 09:15:12','DD/MM/YYYY HH24:MI:SS') as LOGON_DAY,'DURAND' as LOGIN from dual union all
select to_date('02/01/2010 10:12:15','DD/MM/YYYY HH24:MI:SS') as LOGON_DAY,'MARTIN' as LOGIN from dual
),
reqminday as
(
select min(LOGON_DAY) as min from req
),
reqday as
(
select min-1 + level as day from reqminday
connect by level <=
((select max(LOGON_DAY) from req) - (select min(LOGON_DAY) from req)+1)
)
,
reqminheure as
(
select min(to_char(LOGON_DAY,'HH24')) as min from req
),
reqheure as
(
select min-1 + level as heure from reqminheure
connect by level <=
((select max(to_char(LOGON_DAY,'HH24')) from req) - (select min(to_char(LOGON_DAY,'HH24')) from req)+1)
),
reqCalendrier as
(
select rd.day day ,rh.heure heure from reqday rd,reqheure rh
),
avantPivot as (
select day, heure ,sum(total) as total from(
select
day,
heure,
decode(to_char(LOGON_DAY,'DDMMYYYY'),to_char(rc.day,'DDMMYYYY'),decode(to_char(LOGON_DAY,'HH24'),rc.heure,1,0),0) total
from reqCalendrier rc,req r
)
group by day, heure
)
select to_char(day,'DD/MM/YYYY') as dt,
--sum(decode(heure,'7',total,0)) as "08h00",
sum(decode(heure,'8',total,0)) as "08h00",
sum(decode(heure,'9',total,0)) as "09h00",
sum(decode(heure,'10',total,0)) as "10h00"
--sum(decode(heure,'11',total,0)) as "10h00"
--sum(decode(heure,'12',total,0)) as "12h00"
from avantPivot
group by day
order by day

[mongilotti]

merci de marquer ce sujet comme résolu.

[chtom]

Merci