Start Processing : 2024-09-20 02:41:50<BR>0 rows affectedUPDATE db_all_opportunity SET _createdDT = STR_TO_DATE(_createdDate, '%m/%d/%Y')<BR>[1] 0.05 seconds -> 0 rows affected<BR>UPDATE db_all_opportunity SET _closeDT = STR_TO_DATE(_closeDate, '%m/%d/%Y')<BR>[5] 0.01 seconds -> 0 rows affected<BR>UPDATE db_all_opportunity SET _lastStageChangeDT = STR_TO_DATE(_lastStageChangeDate, '%m/%d/%Y') WHERE LENGTH(_lastStageChangeDate)>0<BR>[10] 0.02 seconds -> 0 rows affected<BR>UPDATE db_all_opportunity SET _changeRevenue = IF(_lastNetAnnualizedDealRevenue != _netAnnualizedDealRevenue, 1, 0)<BR>[13] 0.01 seconds -> 0 rows affected<BR>INSERT IGNORE INTO db_opportunity_value_change (_opportunityID, _stage, _closeDate, _before, _after)
SELECT _opportunityID, _stage, _closeDT, _lastNetAnnualizedDealRevenue, _netAnnualizedDealRevenue 
FROM db_all_opportunity
WHERE _changeRevenue = 1 AND _createdDT < DATE_SUB(_lastUpdate, INTERVAL 1 DAY)<BR>[30] 0.01 seconds -> 0 rows affected<BR>UPDATE db_all_opportunity SET _lastNetAnnualizedDealRevenue = _netAnnualizedDealRevenue<BR>[50] 0.01 seconds -> 0 rows affected<BR>UPDATE db_all_opportunity SET _lastUpdate = CURDATE()<BR>[100] 0.1 seconds -> 1935 rows affected<BR>UPDATE db_all_opportunity a, (SELECT MAX(_batchID) _batchID FROM db_all_opportunity) b SET a._deleted = 1 WHERE a._batchID != b._batchID<BR>[110] 0.02 seconds -> 0 rows affected<BR>DELETE FROM db_opportunity_weekly_snapshot<BR>[120] 0.64 seconds -> 7986 rows affected<BR>INSERT IGNORE INTO db_opportunity_weekly_snapshot (_opportunityID, _date)
select DISTINCT a._opportunityID, v.selected_date FROM db_all_opportunity a CROSS JOIN
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 70 DAY) AND DATE_ADD(CURDATE(), INTERVAL 6 DAY)
		AND selected_date <= IF(a._stage = 'Closed Lost', DATE_ADD(a._lastStageChangeDT, INTERVAL 6 DAY), DATE_ADD(CURDATE(), INTERVAL 6 DAY))
		AND selected_date <= IF(a._stage = '5.0 Closed Won', DATE_FORMAT(a._closeDT, '%Y-12-31'), DATE_ADD(CURDATE(), INTERVAL 6 DAY))
		AND selected_date >= a._createdDt
		AND a._deleted = 0
HAVING DAYOFWEEK(selected_date) = 1;<BR>[130] 1.39 seconds -> 7986 rows affected<BR>UPDATE db_opportunity_weekly_snapshot a, db_all_opportunity b SET a._opportunityName = b._opportunityName, a._amount = b._netAnnualizedDealRevenue, a._stage = b._stage, a._fiscalPeriod = b._fiscalPeriod, a._closeDT = b._closeDT, a._category = CASE WHEN b._clinicType = 'Integrated Health Expansion' THEN 'IHE' WHEN b._clinicType = 'Occ Health Upsell' THEN 'Occ Health' ELSE 'New Sales' END
WHERE a._opportunityID = b._opportunityID<BR>[140] 0.3 seconds -> 7986 rows affected<BR>UPDATE db_opportunity_weekly_snapshot a, (SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MIN(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN subdate(curdate(),dayofweek(CURDATE())+5) AND subdate(curdate(),dayofweek(CURDATE())-1)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= subdate(curdate(),dayofweek(CURDATE())+6)
		AND a._opportunityID = b._opportunityID;<BR>[150] 0.08 seconds -> 0 rows affected<BR>UPDATE db_opportunity_weekly_snapshot a, (SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MIN(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN subdate(curdate(),dayofweek(CURDATE())+12) AND subdate(curdate(),dayofweek(CURDATE())+6)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= subdate(curdate(),dayofweek(CURDATE())+13)
		AND a._opportunityID = b._opportunityID;<BR>[160] 0.02 seconds -> 0 rows affected<BR>UPDATE db_opportunity_weekly_snapshot a, (SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MIN(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN subdate(curdate(),dayofweek(CURDATE())+19) AND subdate(curdate(),dayofweek(CURDATE())+13)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= subdate(curdate(),dayofweek(CURDATE())+20)
		AND a._opportunityID = b._opportunityID;<BR>[170] 0.02 seconds -> 0 rows affected<BR>UPDATE db_opportunity_weekly_snapshot a, (SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MIN(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN subdate(curdate(),dayofweek(CURDATE())+26) AND subdate(curdate(),dayofweek(CURDATE())+20)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= subdate(curdate(),dayofweek(CURDATE())+27)
		AND a._opportunityID = b._opportunityID;<BR>[180] 0.01 seconds -> 0 rows affected<BR>UPDATE db_opportunity_weekly_snapshot a, (SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MIN(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN subdate(curdate(),dayofweek(CURDATE())+33) AND subdate(curdate(),dayofweek(CURDATE())+27)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= subdate(curdate(),dayofweek(CURDATE())+34)
		AND a._opportunityID = b._opportunityID;<BR>[190] 0.01 seconds -> 0 rows affected<BR>UPDATE db_opportunity_weekly_snapshot a, (SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MIN(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN subdate(curdate(),dayofweek(CURDATE())+40) AND subdate(curdate(),dayofweek(CURDATE())+34)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= subdate(curdate(),dayofweek(CURDATE())+41)
		AND a._opportunityID = b._opportunityID;<BR>[200] 0.01 seconds -> 0 rows affected<BR>UPDATE db_opportunity_weekly_snapshot a, (SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MIN(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN subdate(curdate(),dayofweek(CURDATE())+47) AND subdate(curdate(),dayofweek(CURDATE())+41)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= subdate(curdate(),dayofweek(CURDATE())+48)
		AND a._opportunityID = b._opportunityID;<BR>[210] 0.01 seconds -> 0 rows affected<BR>UPDATE db_opportunity_weekly_snapshot a, (SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MIN(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN subdate(curdate(),dayofweek(CURDATE())+54) AND subdate(curdate(),dayofweek(CURDATE())+48)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= subdate(curdate(),dayofweek(CURDATE())+55)
		AND a._opportunityID = b._opportunityID;<BR>[220] 0.02 seconds -> 0 rows affected<BR>UPDATE db_opportunity_weekly_snapshot a, (SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MIN(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN subdate(curdate(),dayofweek(CURDATE())+61) AND subdate(curdate(),dayofweek(CURDATE())+55)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= subdate(curdate(),dayofweek(CURDATE())+62)
		AND a._opportunityID = b._opportunityID;<BR>[230] 0.02 seconds -> 0 rows affected<BR>DELETE FROM db_opportunity_monthly_snapshot<BR>[300] 0.38 seconds -> 9438 rows affected<BR>INSERT IGNORE INTO db_opportunity_monthly_snapshot (_opportunityID, _date)
select DISTINCT a._opportunityID, v.selected_date FROM db_all_opportunity a CROSS JOIN
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND LAST_DAY(CURDATE())
		AND selected_date <= IF(a._stage = 'Closed Lost', LAST_DAY(a._lastStageChangeDT), LAST_DAY(CURDATE()))
		AND selected_date <= IF (a._stage = '5.0 Closed Won', DATE_FORMAT(a._closeDT, '%Y-12-31'), LAST_DAY(CURDATE()))
		AND selected_date >= DATE_FORMAT(a._createdDt, '%Y-%m-01')
		AND a._deleted = 0
HAVING selected_date = DATE_FORMAT(selected_date, '%Y-%m-15')<BR>[310] 3.41 seconds -> 8712 rows affected<BR>UPDATE db_opportunity_monthly_snapshot a, db_all_opportunity b SET a._opportunityName = b._opportunityName, a._amount = b._netAnnualizedDealRevenue, a._stage = b._stage, a._fiscalPeriod = b._fiscalPeriod, a._closeDT = b._closeDT, a._category = CASE WHEN b._clinicType = 'Integrated Health Expansion' THEN 'IHE' WHEN b._clinicType = 'Occ Health Upsell' THEN 'Occ Health' ELSE 'New Sales' END
WHERE a._opportunityID = b._opportunityID<BR>[320] 0.33 seconds -> 8712 rows affected<BR>UPDATE db_opportunity_monthly_snapshot a,		
(SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MAX(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN last_day(curdate() - interval 1 month) + interval 1 day AND last_day(curdate() - interval 0 month)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= DATE_FORMAT(last_day(curdate() - interval 1 MONTH),'%Y-%m-15')
	AND a._opportunityID = b._opportunityID;<BR>[330] 0.02 seconds -> 0 rows affected<BR>UPDATE db_opportunity_monthly_snapshot a,		
(SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MAX(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN last_day(curdate() - interval 2 month) + interval 1 day AND last_day(curdate() - interval 1 month)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= DATE_FORMAT(last_day(curdate() - interval 2 MONTH),'%Y-%m-15')
	AND a._opportunityID = b._opportunityID;<BR>[340] 0.02 seconds -> 0 rows affected<BR>UPDATE db_opportunity_monthly_snapshot a,		
(SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MAX(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN last_day(curdate() - interval 3 month) + interval 1 day AND last_day(curdate() - interval 2 month)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= DATE_FORMAT(last_day(curdate() - interval 3 MONTH),'%Y-%m-15')
	AND a._opportunityID = b._opportunityID;<BR>[350] 0.02 seconds -> 0 rows affected<BR>UPDATE db_opportunity_monthly_snapshot a,		
(SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MAX(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN last_day(curdate() - interval 4 month) + interval 1 day AND last_day(curdate() - interval 3 month)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= DATE_FORMAT(last_day(curdate() - interval 4 MONTH),'%Y-%m-15')
	AND a._opportunityID = b._opportunityID;<BR>[360] 0.02 seconds -> 0 rows affected<BR>UPDATE db_opportunity_monthly_snapshot a,		
(SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MAX(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN last_day(curdate() - interval 5 month) + interval 1 day AND last_day(curdate() - interval 4 month)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= DATE_FORMAT(last_day(curdate() - interval 5 MONTH),'%Y-%m-15')
	AND a._opportunityID = b._opportunityID;<BR>[370] 0.02 seconds -> 0 rows affected<BR>UPDATE db_opportunity_monthly_snapshot a,		
(SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MAX(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN last_day(curdate() - interval 6 month) + interval 1 day AND last_day(curdate() - interval 5 month)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= DATE_FORMAT(last_day(curdate() - interval 6 MONTH),'%Y-%m-15')
	AND a._opportunityID = b._opportunityID;<BR>[380] 0.02 seconds -> 0 rows affected<BR>UPDATE db_opportunity_monthly_snapshot a,		
(SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MAX(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN last_day(curdate() - interval 7 month) + interval 1 day AND last_day(curdate() - interval 6 month)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= DATE_FORMAT(last_day(curdate() - interval 7 MONTH),'%Y-%m-15')
	AND a._opportunityID = b._opportunityID;<BR>[390] 0.02 seconds -> 0 rows affected<BR>UPDATE db_opportunity_monthly_snapshot a,		
(SELECT _opportunityID, IF(_fromStage = 'Non Qualified','1.0 Non-Qualified',_fromStage) _fromStage, IF(_toStage = 'Non Qualified','1.0 Non-Qualified', _toStage) _toStage, MAX(_lastModifiedDT) FROM db_opportunity_stage_change
WHERE _toStage != _fromStage
AND _lastModifiedDT BETWEEN last_day(curdate() - interval 8 month) + interval 1 day AND last_day(curdate() - interval 7 month)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= DATE_FORMAT(last_day(curdate() - interval 8 MONTH),'%Y-%m-15')
	AND a._opportunityID = b._opportunityID;<BR>[400] 0.02 seconds -> 0 rows affected<BR>