Start Processing : 2026-11-18 03:38:30
0 rows affectedUPDATE db_careatc_all_opportunities SET _createdDT = STR_TO_DATE(_createdDate, '%m/%d/%Y')
[10] 0.07 seconds -> 0 rows affected
UPDATE db_careatc_all_opportunities SET _closeDT = STR_TO_DATE(_closeDate, '%m/%d/%Y')
[20] 0.02 seconds -> 0 rows affected
UPDATE db_careatc_all_opportunities SET _lastStageChangeDT = STR_TO_DATE(_lastStageChangeDate, '%m/%d/%Y') WHERE LENGTH(_lastStageChangeDate)>0
[30] 0.02 seconds -> 0 rows affected
UPDATE db_careatc_all_opportunities SET _changeRevenue = IF(_lastNetAnnualizedDealRevenue != _netAnnualizedDealRevenue, 1, 0)
[40] 0.02 seconds -> 0 rows affected
INSERT IGNORE INTO db_opportunity_value_change (_opportunityID, _stage, _closeDate, _before, _after)
SELECT _opportunityID, _stage, _closeDT, _lastNetAnnualizedDealRevenue, _netAnnualizedDealRevenue
FROM db_careatc_all_opportunities
WHERE _changeRevenue = 1 AND _createdDT < DATE_SUB(_lastUpdate, INTERVAL 1 DAY)
[50] 0.02 seconds -> 57 rows affected
UPDATE db_careatc_all_opportunities SET _lastNetAnnualizedDealRevenue = _netAnnualizedDealRevenue
[60] 0.01 seconds -> 0 rows affected
UPDATE db_careatc_all_opportunities SET _lastUpdate = CURDATE()
[70] 0.07 seconds -> 2006 rows affected
UPDATE db_careatc_all_opportunities a, (SELECT MAX(_batchID) _batchID FROM db_careatc_all_opportunities ) b SET a._deleted = 1 WHERE a._batchID != b._batchID
[80] 0.01 seconds -> 0 rows affected
DELETE FROM db_opportunity_weekly_snapshot
[90] 0.3 seconds -> 7986 rows affected
INSERT IGNORE INTO db_opportunity_weekly_snapshot (_opportunityID, _date)
select DISTINCT a._opportunityID, v.selected_date FROM db_careatc_all_opportunities 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;
[100] 1.4 seconds -> 8525 rows affected
UPDATE db_opportunity_weekly_snapshot a, db_careatc_all_opportunities 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 LIKE '%lient Upsell Collaborative%' THEN 'Collaborative Care/Site Expansion' WHEN b._clinicType LIKE '%lient Upsell Site Expans%' THEN 'Collaborative Care/Site Expansion' WHEN b._clinicType LIKE '%Upsell Occ H%' THEN 'Occ Health' ELSE 'New Sales' END, a._division = b._division
WHERE a._opportunityID = b._opportunityID
[110] 0.38 seconds -> 8525 rows affected
UPDATE db_opportunity_weekly_snapshot a, db_careatc_all_opportunities b
SET a._division = IF(b._opportunityOwner IN ('Scott Parks','Brock Hughes'), 'CareTEAM', a._division)
WHERE a._opportunityID = b._opportunityID
[115] 0.12 seconds -> 286 rows affected
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;
[120] 0.02 seconds -> 0 rows affected
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;
[130] 0.02 seconds -> 0 rows affected
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;
[140] 0.02 seconds -> 0 rows affected
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;
[150] 0.02 seconds -> 0 rows affected
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;
[160] 0.02 seconds -> 0 rows affected
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;
[170] 0.02 seconds -> 0 rows affected
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;
[180] 0.02 seconds -> 0 rows affected
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;
[190] 0.02 seconds -> 0 rows affected
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;
[200] 0.02 seconds -> 0 rows affected
DELETE FROM db_opportunity_monthly_snapshot
[210] 0.43 seconds -> 8712 rows affected
INSERT IGNORE INTO db_opportunity_monthly_snapshot (_opportunityID, _date)
select DISTINCT a._opportunityID, v.selected_date FROM db_careatc_all_opportunities 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')
[220] 4.54 seconds -> 9300 rows affected
UPDATE db_opportunity_monthly_snapshot a, db_careatc_all_opportunities 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 LIKE '%lient Upsell Collaborative%' THEN 'Collaborative Care/Site Expansion' WHEN b._clinicType LIKE '%lient Upsell Site Expans%' THEN 'Collaborative Care/Site Expansion' WHEN b._clinicType LIKE '%Upsell Occ H%' THEN 'Occ Health' ELSE 'New Sales' END, a._division = b._division
WHERE a._opportunityID = b._opportunityID
[230] 0.41 seconds -> 9300 rows affected
UPDATE db_opportunity_monthly_snapshot a, db_careatc_all_opportunities b
SET a._division = IF(b._opportunityOwner IN ('Scott Parks','Brock Hughes'), 'CareTEAM', a._division)
WHERE a._opportunityID = b._opportunityID
[235] 0.12 seconds -> 312 rows affected
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;
[240] 0.02 seconds -> 0 rows affected
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;
[250] 0.02 seconds -> 0 rows affected
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;
[260] 0.02 seconds -> 0 rows affected
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;
[270] 0.02 seconds -> 0 rows affected
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;
[280] 0.02 seconds -> 0 rows affected
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;
[290] 0.02 seconds -> 0 rows affected
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;
[300] 0.02 seconds -> 0 rows affected
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;
[310] 0.03 seconds -> 0 rows affected
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 9 month) + interval 1 day AND last_day(curdate() - interval 8 month)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= DATE_FORMAT(last_day(curdate() - interval 9 MONTH),'%Y-%m-15')
AND a._opportunityID = b._opportunityID;
[320] 0.02 seconds -> 0 rows affected
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 10 month) + interval 1 day AND last_day(curdate() - interval 9 month)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= DATE_FORMAT(last_day(curdate() - interval 10 MONTH),'%Y-%m-15')
AND a._opportunityID = b._opportunityID;
[330] 0.02 seconds -> 0 rows affected
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 11 month) + interval 1 day AND last_day(curdate() - interval 10 month)
AND _fromStage !=''
GROUP BY 1,2,3) b
SET a._stage = b._fromStage
WHERE a._date <= DATE_FORMAT(last_day(curdate() - interval 11 MONTH),'%Y-%m-15')
AND a._opportunityID = b._opportunityID;
[340] 0.02 seconds -> 0 rows affected