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
| SELECT
m.idmembre,
m.prenom as 'prénom',
m.nom,
m.connu,
COUNT(x1.idparticipation) as 'nombre pointage',
COUNT(i1.idinscription) as 'nombre pointage',
SUM( IF( joursemaine = 2, 1, 0 ) ) as 'Lundi',
SUM( IF( joursemaine = 3, 1, 0 ) ) as 'Mardi',
SUM( IF( joursemaine = 4, 1, 0 ) ) as 'Mercredi',
SUM( IF( joursemaine = 5, 1, 0 ) ) as 'Jeudi',
SUM( IF( joursemaine = 6, 1, 0 ) ) as 'Vendredi',
SUM( IF( joursemaine = 7, 1, 0 ) ) as 'Samedi',
SUM( IF( joursemaine = 1, 1, 0 ) ) as 'Dimanche'
FROM gestion_membre AS m
LEFT JOIN gestion_participation as x1 ON x1.idmembre = m.idmembre
INNER JOIN
(
SELECT
s2.idsession,
s2.date_session,
DAYOFWEEK(s2.date_session) as joursemaine,
s2.idplanning
FROM gestion_session as s2
WHERE "2010-08-01 00:00:00" <= s2.date_session AND s2.date_session <= "2011-08-01 00:00:00"
) as s1 ON x1.idsession = s1.idsession
LEFT JOIN
(
SELECT
i2.idinscription,
i2.idmembre,
i2.idplanning,
i2.carte
FROM gestion_inscription as i2
WHERE "2010-08-01 00:00:00" <= i2.date_debut AND i2.date_debut <= "2011-08-01 00:00:00" )
) as i1 ON i1.idmembre = m.idmembre
GROUP BY m.idmembre |
