SELECT
FECHAS.ANIO AS ANIO, FECHAS.MES AS MES, SEL_INT . *
FROM
(SELECT
SEL2.PROYECTO,
SEL2.CLAVE,
SUM(SEL2.IMPUTADO),
SEL2.MES_GAR,
SEL2.INICIO_GAR AS INICIO_GAR,
SEL2.FIN_GAR,
SEL2.DURACION_GAR AS DURACION_GAR,
SEL2.MES_IMP AS MES_IMP,
SEL2.ANO_IMP
FROM
(SELECT
SEL1.PRO AS PROYECTO,
SEL1.PROKEY AS CLAVE,
LOG.`timeworked` AS IMPUTADO,
PERIOD_DIFF(DATE_FORMAT(LOG.STARTDATE, '%Y%m'), DATE_FORMAT(SEL1.INICIO, '%Y%m')) + 1 AS MES_GAR,
SEL1.INICIO AS INICIO_GAR,
SEL1.FIN AS FIN_GAR,
SEL1.DURACION AS DURACION_GAR,
DATE_FORMAT(LOG.STARTDATE, '%m') AS MES_IMP,
DATE_FORMAT(LOG.STARTDATE, '%Y') AS ANO_IMP
FROM
(SELECT
PRO.ID AS PROID,
PRO.`pname` AS PRO,
PRO.`pkey` AS PROKEY,
ISSUE.`pkey` AS PKEY,
(SELECT
NUMBERVALUE
FROM
JIRA42.`customfieldvalue` AS VAL
WHERE
VAL.`customfield` = '10470'
AND VAL.ISSUE = ISSUE.ID) AS DURACION,
(SELECT
DATEVALUE
FROM
JIRA42.`customfieldvalue` AS VAL
WHERE
VAL.`customfield` = '10081'
AND VAL.ISSUE = ISSUE.ID) AS INICIO,
(SELECT
DATEVALUE
FROM
JIRA42.`customfieldvalue` AS VAL
WHERE
VAL.`customfield` = '10455'
AND VAL.ISSUE = ISSUE.ID) AS FIN
FROM
JIRA42.`jiraissue` AS ISSUE
JOIN JIRA42.`project` AS PRO ON (PRO.ID = ISSUE.PROJECT
AND ISSUE.`issuetype` = '13')
JOIN JIRA42.`customfieldvalue` AS CFV ON (CFV.ISSUE = ISSUE.ID
AND CFV.`customfield` = '10087'
AND CFV.STRINGVALUE = 'Fase 07 - Mantenimiento Correctivo')) AS SEL1, JIRA42.`worklog` AS LOG
JOIN JIRA42.`jiraissue` AS ISSUE2 ON (LOG.`issueid` = ISSUE2.ID)
WHERE
ISSUE2.`project` = SEL1.PROID
AND LOG.STARTDATE BETWEEN SEL1.INICIO AND SEL1.FIN) SEL2
GROUP BY PROYECTO , MES_IMP , ANO_IMP) AS SEL_INT
RIGHT OUTER JOIN
(SELECT
ANIO.year_id AS ANIO, MES.a_month_id AS MES
FROM
all_years AS ANIO, all_months AS MES) AS FECHAS ON (FECHAS.ANIO = SEL_INT.ANO_IMP
AND FECHAS.MES = SEL_INT.MES_IMP
AND FECHAS.ANIO BETWEEN DATE_FORMAT(SEL_INT.INICIO_GAR, '%Y') AND DATE_FORMAT(DATE_ADD(SEL_INT.INICIO_GAR,
INTERVAL DURACION_GAR MONTH),
'%Y'))
ORDER BY ANIO , MES;