SELECT
CONCAT(SUBSTRING_INDEX(u_name,' ',-1),' ',SUBSTRING_INDEX(u_name,' ',1)) AS u_name,
p_name,
a_name,
DAYNAME(al_date) AS jour,
extract(year FROM al_date) AS annee,
CASE extract(month FROM al_date)
WHEN 1 THEN '01 - Janvier'
WHEN 2 THEN '02 - Février'
WHEN 3 THEN '03 - Mars'
WHEN 4 THEN '04 - Avril'
WHEN 5 THEN '05 - Mai'
WHEN 6 THEN '06 - Juin'
WHEN 7 THEN '07 - Juillet'
WHEN 8 THEN '08 - Août'
WHEN 9 THEN '09 - Septembre'
WHEN 10 THEN '10 - Octobre'
WHEN 11 THEN '11 - Novembre'
WHEN 12 THEN '12 - Décembre'
END AS mois,
round(time_to_sec(al_duration)/3600/(CASE WHEN DAYNAME(al_date)='FRIDAY' THEN 7 ELSE 8 END),2) AS duree
FROM cenlr_temps_travail.activity_log
JOIN cenlr_temps_travail.projects ON al_project_id = p_id
JOIN cenlr_temps_travail.activities ON al_activity_id = a_id
JOIN cenlr_temps_travail.users ON al_user_id = u_id
WHERE extract(year FROM al_date) = 2011
AND al_activity_id <> 402 AND al_activity_id <> 388
ORDER BY trim(substring(p_name FROM 1 FOR (locate('-', p_name)-1))), 2
SELECT
u_name,
p_name,
extract(month FROM al_date) AS mois,
round(sum(time_to_sec(al_duration))/3600/7.8,2),
prev_duration,
prev_tot
FROM cenlr_temps_travail.activity_log
JOIN cenlr_temps_travail.projects ON al_project_id = p_id
JOIN cenlr_temps_travail.activities ON al_activity_id = a_id
JOIN cenlr_temps_travail.users ON al_user_id = u_id
JOIN cenlr_temps_travail.prev ON prev_user_id = u_id AND prev_proj_id = p_id
JOIN (SELECT prev_year AS prev_annee, prev_user_id AS prev_u_id, sum(prev_duration) AS prev_tot FROM cenlr_temps_travail.prev
GROUP BY prev_year, prev_user_id) prev_tot ON prev_annee = extract(year FROM al_date) AND prev_u_id = prev_user_id
WHERE u_name = 'Mathieu Bossaert'
AND extract(year FROM al_date) = 2011 AND prev_year = 2011
GROUP BY u_name, p_name, extract(month FROM al_date)
ORDER BY 3
SELECT
u_name,
p_name,
CASE extract(month FROM al_date)
WHEN 1 THEN 'Janvier'
WHEN 2 THEN 'Février'
WHEN 3 THEN 'Mars'
WHEN 4 THEN 'Avril'
WHEN 5 THEN 'Mai'
WHEN 6 THEN 'Juin'
WHEN 7 THEN 'Juillet'
WHEN 8 THEN 'Août'
WHEN 9 THEN 'Septembre'
WHEN 10 THEN 'Octobre'
WHEN 11 THEN 'Novembre'
WHEN 12 THEN 'Décembre'
END AS mois,
round(sum(time_to_sec(al_duration))/3600/7.8,2),
prev_duration
FROM cenlr_temps_travail.activity_log
JOIN cenlr_temps_travail.projects ON al_project_id = p_id
JOIN cenlr_temps_travail.activities ON al_activity_id = a_id
JOIN cenlr_temps_travail.users ON al_user_id = u_id
JOIN cenlr_temps_travail.prev ON prev_user_id = u_id AND prev_proj_id = p_id
AND extract(year FROM al_date) = 2011 AND prev_year = 2011
WHERE p_id = 293
GROUP BY u_name, p_name, extract(month FROM al_date)
ORDER BY 3
