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
|
SELECT
c.date_calendrier,
reel.type_temps AS reel, reel.temps,
previsionnel.type_temps AS previsionnel, previsionnel.temps
FROM
affectation a
INNER JOIN
calendrier c
ON
c.id_tache = a.id_tache AND
c.id_sfguarduser = a.id_sfguarduser
LEFT OUTER JOIN
(SELECT
SUM(c1.temps) AS temps, c1.type_temps, c1.id_tache, c1.id_sfguarduser, c1.date_calendrier
FROM
calendrier c1
WHERE
c1.type_temps = "R"
GROUP BY
c1.date_calendrier, c1.id_sfguarduser
)AS reel
ON
c.id_sfguarduser = reel.id_sfguarduser AND
c.date_calendrier = reel.date_calendrier
LEFT OUTER JOIN
(SELECT
SUM(c2.temps) AS temps, c2.type_temps, c2.id_tache, c2.id_sfguarduser, c2.date_calendrier
FROM
calendrier c2
WHERE
c2.type_temps = "P"
GROUP BY
c2.date_calendrier, c2.id_sfguarduser
)AS previsionnel
ON
c.id_sfguarduser = previsionnel.id_sfguarduser AND
c.date_calendrier = previsionnel.date_calendrier
WHERE
c.id_sfguarduser = '2' AND
c.date_calendrier >= '2012-01-01' AND
c.date_calendrier < '2012-02-01'
GROUP BY
c.date_calendrier |
Partager