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
| SELECT
idplanning1,
idplanning2,
SUM( nombreAssite1 ) /
(
SELECT COUNT(x2.idparticipation)
FROM gestion_session as s2 INNER JOIN gestion_participation as x2 ON s2.idsession = x2.idsession
WHERE s2.idplanning = idplanning1
AND "2010-08-01 00:00:00" <= s2.date_session AND s2.date_session <= "2011-08-01 00:00:00"
) *
SUM( nombreAssite2 ) /
(
SELECT COUNT(x4.idparticipation)
FROM gestion_session as s4 INNER JOIN gestion_participation as x4 ON s4.idsession = x4.idsession
WHERE s4.idplanning = idplanning2
AND
"2010-08-01
00:00:00" <= s4.date_session AND s4.date_session <= "2011-08-01 00:00:00"
) as taux
FROM
(
SELECT
idplanning1,
idplanning2,
idmembre,
nombreAssite1,
nombreAssite2
FROM
(
SELECT
p1.idplanning as idplanning1,
p2.idplanning as idplanning2,
m.idmembre,
(
SELECT COUNT(x1.idparticipation)
FROM gestion_participation as x1 INNER JOIN gestion_session as s1 ON s1.idsession = x1.idsession
WHERE x1.idmembre = m.idmembre
AND s1.idplanning = p1.idplanning
AND "2010-08-01 00:00:00" <= s1.date_session AND s1.date_session <= "2011-08-01 00:00:00"
) as nombreAssite1,
(
SELECT COUNT(x3.idparticipation)
FROM gestion_participation as x3 INNER JOIN gestion_session as s3 ON s3.idsession = x3.idsession
WHERE x3.idmembre = m.idmembre
AND s3.idplanning = p2.idplanning
AND
"2010-08-01
00:00:00" <=
s3.date_session AND s3.date_session <= "2011-08-01 00:00:00"
) as nombreAssite2
FROM gestion_membre as m ,
gestion_planning as p1,
gestion_planning as p2
WHERE "2010-08-01 00:00:00" <= p1.date_fin AND p1.date_debut <= "2011-08-01 00:00:00"
AND "2010-08-01 00:00:00" <= p2.date_fin AND p2.date_debut <= "2011-08-01 00:00:00"
) AS t1
WHERE nombreAssite1 > 0
) as t2
GROUP BY idplanning1, idplanning2 |
Partager