INSERT IGNORE INTO dashboard_monthly_stagechange (_opportunityID, _changeTS, _changeDT, _mm, _yy, _dd, _event,_stageBefore, _stageAfter)
SELECT d._opportunityID, _editTS, UNIX_TIMESTAMP(_editTS), MONTH(_editTS), YEAR(_editTS),DAY(_editTS),'Closed Won',_before,_after
FROM dashboard_opportunity d
INNER JOIN
(SELECT _opportunityID, _before, _after, MIN(_editTS) '_editTS'
FROM dashboard_opportunity_changelog
WHERE _after = 'Closed Won'
AND _field = 'Stage'
AND _before <> _after
GROUP BY _opportunityID, _before, _after) o
WHERE d._opportunityID = o._opportunityID
INSERT IGNORE INTO dashboard_monthly_stagechange (_opportunityID, _changeTS, _changeDT, _mm, _yy, _dd, _event,_stageBefore, _stageAfter)
SELECT d._opportunityID, _editTS, UNIX_TIMESTAMP(_editTS), MONTH(_editTS), YEAR(_editTS),DAY(_editTS),'Closed Lost',_before,_after
FROM dashboard_opportunity d
INNER JOIN
(SELECT _opportunityID, _before, _after, MIN(_editTS) '_editTS'
FROM dashboard_opportunity_changelog
WHERE _after = 'Closed Lost'
AND _field = 'Stage'
AND _before <> _after
GROUP BY _opportunityID, _before, _after) o
WHERE d._opportunityID = o._opportunityID
INSERT IGNORE INTO dashboard_monthly_stagechange (_opportunityID, _changeTS, _changeDT, _mm, _yy, _dd, _event,_stageBefore, _stageAfter)
SELECT d._opportunityID, _editTS, UNIX_TIMESTAMP(_editTS), MONTH(_editTS), YEAR(_editTS),DAY(_editTS),'Not Qualified',_before,_after
FROM dashboard_opportunity d
INNER JOIN
(SELECT _opportunityID, _before, _after, MIN(_editTS) '_editTS'
FROM dashboard_opportunity_changelog
WHERE _after = 'Not Qualified'
AND _field = 'Stage'
AND _before <> _after
GROUP BY _opportunityID, _before, _after) o
WHERE d._opportunityID = o._opportunityID
INSERT IGNORE INTO dashboard_monthly_stagechange (_opportunityID, _changeTS, _changeDT, _mm, _yy, _dd, _event,_stageBefore, _stageAfter)
SELECT d._opportunityID, _editTS, UNIX_TIMESTAMP(_editTS), MONTH(_editTS), YEAR(_editTS),DAY(_editTS),'Qualified',_before,_after
FROM dashboard_opportunity d
INNER JOIN
(SELECT _opportunityID, _before, _after, MIN(_editTS) '_editTS'
FROM dashboard_opportunity_changelog
WHERE _after = 'Qualified'
AND _field = 'Stage'
AND _before <> _after
GROUP BY _opportunityID, _before, _after) o
WHERE d._opportunityID = o._opportunityID
INSERT IGNORE INTO dashboard_monthly_stagechange (_opportunityID, _changeTS, _changeDT, _mm, _yy, _dd, _event,_stageBefore, _stageAfter)
SELECT d._opportunityID, _editTS, UNIX_TIMESTAMP(_editTS), MONTH(_editTS), YEAR(_editTS),DAY(_editTS),'Best Few',_before,_after
FROM dashboard_opportunity d
INNER JOIN
(SELECT _opportunityID, _before, _after, MIN(_editTS) '_editTS'
FROM dashboard_opportunity_changelog
WHERE _after = 'Best Few'
AND _field = 'Stage'
AND _before <> _after
GROUP BY _opportunityID, _before, _after) o
WHERE d._opportunityID = o._opportunityID
INSERT IGNORE INTO dashboard_monthly_stagechange (_opportunityID, _changeTS, _changeDT, _mm, _yy, _dd, _event,_stageBefore, _stageAfter)
SELECT d._opportunityID, _editTS, UNIX_TIMESTAMP(_editTS), MONTH(_editTS), YEAR(_editTS),DAY(_editTS),'VOC/Contracting',_before,_after
FROM dashboard_opportunity d
INNER JOIN
(SELECT _opportunityID, _before, _after, MIN(_editTS) '_editTS'
FROM dashboard_opportunity_changelog
WHERE _after = 'VOC/Contracting'
AND _field = 'Stage'
AND _before <> _after
GROUP BY _opportunityID, _before, _after) o
WHERE d._opportunityID = o._opportunityID