Start Processing : 2024-09-20 02:59:34<BR>0 rows affectedUPDATE db_workfront_utilization 
SET _createdDT = STR_TO_DATE(_entryDate, '%m/%d/%Y') 
WHERE _entryDate <> ''<BR>[1] 0.05 seconds -> 0 rows affected<BR>UPDATE db_workfront_utilization 
SET _completedDT = STR_TO_DATE(_actualCompletionDate,'%m/%d/%Y')
WHERE _actualCompletionDate <> ''<BR>[2] 0 seconds -> 0 rows affected<BR>UPDATE db_workfront_utilization 
SET _dueDT = STR_TO_DATE(_dueOn,'%m/%d/%Y')
WHERE _dueOn <> ''<BR>[3] 0.01 seconds -> 0 rows affected<BR>UPDATE db_workfront_utilization 
SET _predictCompleteDT = IF(_completedDT IS NULL, 
	IF(_dueDT IS NOT NULL, IF(NOW() >= _dueDT, 
		IF(NOW() >= DATE_ADD(_dueDT, INTERVAL 1 MONTH), _dueDT,NOW()), _dueDT),NULL), _completedDT)<BR>[4] 0.03 seconds -> 0 rows affected<BR>UPDATE db_workfront_utilization a
LEFT JOIN db_cpm_standard_workflow b
ON a._contentType = b._request
SET a._designBrandingHours = b._designBranding,
	 a._contentWritingHours = b._contentWriting,
	 a._videoAnimationHours = b._videoAnimation<BR>[5] 0.33 seconds -> 0 rows affected<BR>UPDATE db_workfront_utilization 
SET _predictCategory = IF(_predictCompleteDT = _completedDT, 'Completed', IF(_predictCompleteDT = _dueDT, 'Actual Due Date', CONCAT('delayed ',DATEDIFF(_predictCompleteDT,_dueDT),' day(s)')))<BR>[6] 0.01 seconds -> 0 rows affected<BR>INSERT INTO db_cpm_month_snapshot(_monthYear)

(SELECT DISTINCT a.YearMonths
FROM (SELECT DISTINCT(DATE_FORMAT(_createdDT, '%Y%m')) AS 'YearMonths'
			FROM db_workfront_utilization) a	
WHERE a.YearMonths NOT IN (SELECT _monthYear FROM db_cpm_month_snapshot)
GROUP BY a.YearMonths
ORDER BY a.YearMonths)

UNION ALL

(SELECT CASE
	WHEN SUBSTRING(a._monthYear, 5, 2) = 12 THEN CONCAT(SUBSTRING(a._monthYear, 1, 4) + 1, '01')
	ELSE CONCAT(SUBSTRING(a._monthYear, 1, 4), LPAD(SUBSTRING(a._monthYear, 5, 2)+1, 2,'0'))
END
FROM (SELECT _monthYear, _id 
		FROM db_cpm_month_snapshot 
		ORDER BY _id DESC 
		LIMIT 1) a
WHERE LPAD(MONTH(CURDATE()), 2, '0') = LPAD(SUBSTRING(a._monthYear, 5, 2), 2,'0'))<BR>[7] 0.01 seconds -> 0 rows affected<BR>UPDATE db_cpm_month_snapshot a 
INNER JOIN (SELECT DATE_FORMAT(_createdDT, '%Y%m') AS _monthYear, COUNT(*) _taskCreated 
					FROM db_workfront_utilization
					WHERE _createdDT IS NOT NULL
					AND _createdDT > '2023-01-31'
					GROUP BY _monthYear 
					ORDER BY _monthYear DESC) b
ON a._monthYear = b._monthYear
SET a._taskCreated = b._taskCreated<BR>[8] 0 seconds -> 0 rows affected<BR>UPDATE db_cpm_month_snapshot a 
INNER JOIN (SELECT DATE_FORMAT(_completedDT, '%Y%m') AS _monthYear, COUNT(_completedDT) _taskCompleted 
					FROM db_workfront_utilization 
					WHERE _completedDT IS NOT NULL 
					AND _completedDT > '2023-01-31'
					GROUP BY _monthYear 
					ORDER BY _monthYear DESC ) b
ON a._monthYear = b._monthYear
SET a._taskCompleted = b._taskCompleted<BR>[9] 0 seconds -> 0 rows affected<BR>UPDATE db_cpm_month_snapshot a
INNER JOIN 
	(SELECT _monthYear, 5 * (DATEDIFF(LAST_DAY(STR_TO_DATE(CONCAT(SUBSTRING(_monthYear, 1, 4), '-', SUBSTRING(_monthYear, 5, 2), '-', '01'), '%Y-%m-%d')), 
		STR_TO_DATE(CONCAT(SUBSTRING(_monthYear, 1, 4), '-', SUBSTRING(_monthYear, 5, 2), '-', '01'), '%Y-%m-%d')) DIV 7) + 
		MID('1234555512344445123333451222234511112345001234550', 
		7 * WEEKDAY(STR_TO_DATE(CONCAT(SUBSTRING(_monthYear, 1, 4), '-', SUBSTRING(_monthYear, 5, 2), '-', '01'), '%Y-%m-%d')) + 
		WEEKDAY(LAST_DAY(STR_TO_DATE(CONCAT(SUBSTRING(_monthYear, 1, 4), '-', SUBSTRING(_monthYear, 5, 2), '-', '01'), '%Y-%m-%d'))) + 1, 1) AS _workingDays
	FROM db_cpm_month_snapshot
	WHERE _workingDays = 0) b
ON a._monthYear = b._monthYear
SET a._workingDays = b._workingDays<BR>[10] 0 seconds -> 0 rows affected<BR>UPDATE db_cpm_month_snapshot 
SET _designBrandingCapacity = (SELECT _headCount FROM db_cpm_team_headcount WHERE _team = 'Design/Branding') * _workingDays * 8<BR>[11] 0 seconds -> 0 rows affected<BR>UPDATE db_cpm_month_snapshot
SET _contentWritingCapacity = (SELECT _headCount FROM db_cpm_team_headcount WHERE _team = 'Content/Writing') * _workingDays * 8<BR>[12] 0 seconds -> 0 rows affected<BR>UPDATE db_cpm_month_snapshot 
SET _videoAnimationCapacity = (SELECT _headCount FROM db_cpm_team_headcount WHERE _team = 'Video/Animation') * _workingDays * 8<BR>[13] 0 seconds -> 0 rows affected<BR>UPDATE db_cpm_month_snapshot a INNER JOIN 
(SELECT DATE_FORMAT(_completedDT, '%Y%m') AS _monthYear, SUM(_designBrandingHours) AS _designBrandingCompletedHours
FROM db_workfront_utilization 
WHERE _completedDT IS NOT NULL
AND _completedDT > '2023-01-31' 
GROUP BY _monthYear 
ORDER BY _monthYear DESC) b
ON a._monthYear = b._monthYear
SET a._designBrandingCompletedHours = b._designBrandingCompletedHours<BR>[14] 0 seconds -> 0 rows affected<BR>UPDATE db_cpm_month_snapshot a INNER JOIN 
(SELECT DATE_FORMAT(_completedDT, '%Y%m') AS _monthYear, SUM(_contentWritingHours) AS _contentWritingCompletedHours
FROM db_workfront_utilization 
WHERE _completedDT IS NOT NULL
AND _completedDT > '2023-01-31' 
GROUP BY _monthYear 
ORDER BY _monthYear DESC) b
ON a._monthYear = b._monthYear
SET a._contentWritingCompletedHours = b._contentWritingCompletedHours<BR>[15] 0 seconds -> 0 rows affected<BR>UPDATE db_cpm_month_snapshot a INNER JOIN 
(SELECT DATE_FORMAT(_completedDT, '%Y%m') AS _monthYear, SUM(_videoAnimationHours) AS _videoAnimationCompletedHours
FROM db_workfront_utilization 
WHERE _completedDT IS NOT NULL 
AND _completedDT > '2023-01-31'
GROUP BY _monthYear 
ORDER BY _monthYear DESC) b
ON a._monthYear = b._monthYear
SET a._videoAnimationCompletedHours = b._videoAnimationCompletedHours<BR>[16] 0 seconds -> 0 rows affected<BR>UPDATE db_cpm_month_snapshot 
SET _designBrandingUtilization = _designBrandingCompletedHours/_designBrandingCapacity,
	 _contentWritingUtilization = _contentWritingCompletedHours/_contentWritingCapacity,
	 _videoAnimationUtilization = _videoAnimationCompletedHours/_videoAnimationCapacity<BR>[17] 0 seconds -> 0 rows affected<BR>UPDATE db_cpm_month_snapshot a INNER JOIN 
(SELECT DATE_FORMAT(_predictCompleteDT, '%Y%m') AS _monthYear, SUM(_designBrandingHours) AS _designBrandingPredictedHours
FROM db_workfront_utilization
WHERE _predictCompleteDT IS NOT NULL
AND _predictCompleteDT > '2023-01-31' 
GROUP BY _monthYear 
ORDER BY _monthYear DESC) b
ON a._monthYear = b._monthYear
SET a._designBrandingPredictedHours = b._designBrandingPredictedHours<BR>[18] 0.03 seconds -> 4 rows affected<BR>UPDATE db_cpm_month_snapshot a INNER JOIN 
(SELECT DATE_FORMAT(_predictCompleteDT, '%Y%m') AS _monthYear, SUM(_contentWritingHours) AS _contentWritingPredictedHours
FROM db_workfront_utilization 
WHERE _predictCompleteDT IS NOT NULL
AND _predictCompleteDT > '2023-01-31' 
GROUP BY _monthYear 
ORDER BY _monthYear DESC) b
ON a._monthYear = b._monthYear
SET a._contentWritingPredictedHours = b._contentWritingPredictedHours<BR>[19] 0.02 seconds -> 4 rows affected<BR>UPDATE db_cpm_month_snapshot a INNER JOIN 
(SELECT DATE_FORMAT(_predictCompleteDT, '%Y%m') AS _monthYear, SUM(_videoAnimationHours) AS _videoAnimationPredictedHours
FROM db_workfront_utilization
WHERE _predictCompleteDT IS NOT NULL 
AND _predictCompleteDT > '2023-01-31'
GROUP BY _monthYear 
ORDER BY _monthYear DESC) b
ON a._monthYear = b._monthYear
SET a._videoAnimationPredictedHours = b._videoAnimationPredictedHours<BR>[20] 0.02 seconds -> 4 rows affected<BR>UPDATE db_cpm_month_snapshot 
SET _designBrandingPredictedUtilization = _designBrandingPredictedHours/_designBrandingCapacity,
	 _contentWritingPredictedUtilization = _contentWritingPredictedHours/_contentWritingCapacity,
	 _videoAnimationPredictedUtilization = _videoAnimationPredictedHours/_videoAnimationCapacity<BR>[21] 0.04 seconds -> 4 rows affected<BR>INSERT INTO db_cpm_day_snapshot(_date)

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(CONCAT(x.CreatedYear, '-', x.CreatedMonth, '-', t.n)),'%Y-%m-%d') AS DATE 
FROM (
	     SELECT (((b4.0 << 1 | b3.0) << 1 | b2.0) << 1 | b1.0) << 1 | b0.0 AS n
	             FROM  (SELECT 0 UNION ALL SELECT 1) AS b0,
	                   (SELECT 0 UNION ALL SELECT 1) AS b1,
	                   (SELECT 0 UNION ALL SELECT 1) AS b2,
	                   (SELECT 0 UNION ALL SELECT 1) AS b3,
	                   (SELECT 0 UNION ALL SELECT 1) AS b4 ) t,
                      
		 (SELECT DISTINCT CreatedMonth, CreatedYear,
				DAY(LAST_DAY(STR_TO_DATE(CONCAT(a.CreatedYear, '-', a.CreatedMonth, '-', '01'), '%Y-%m-%d'))) AS 'DaysInMonth'
			FROM 
				(SELECT DISTINCT MONTH(_createdDT) AS 'CreatedMonth', YEAR(_createdDT) AS 'CreatedYear'
				FROM db_workfront_utilization) a
			WHERE CONCAT(a.CreatedYear, a.CreatedMonth) NOT IN (SELECT CONCAT(YEAR(_date), MONTH(_date)) FROM db_cpm_day_snapshot)
			ORDER BY a.CreatedYear, a.CreatedMonth) x
									
WHERE t.n > 0 AND t.n <= x.DaysInMonth<BR>[22] 0.04 seconds -> 0 rows affected<BR>UPDATE db_cpm_day_snapshot a INNER JOIN (SELECT _completedDT, SUM(_designBrandingHours) AS _designBrandingCompletedHours, SUM(_contentWritingHours) AS _contentWritingCompletedHours, SUM(_videoAnimationHours) AS _videoAnimationCompletedHours
FROM db_workfront_utilization
WHERE _completedDT IS NOT NULL
		AND _completedDT >= '2022-12-31'
GROUP BY _completedDT
ORDER BY _completedDT DESC) b
ON a._date = b._completedDT
SET a._designBrandingCompletedHours = b._designBrandingCompletedHours,
	 a._contentWritingCompletedHours = b._contentWritingCompletedHours,
	 a._VideoAnimationCompletedHours = b._videoAnimationCompletedHours<BR>[23] 0.03 seconds -> 6 rows affected<BR>