This document describes the end-to-end SQL process we run for JaNee prospecting. It’s written so you can copy/paste each block as needed. The run is scoped to “today” via ScoreDate = DATE(NOW()).
Inputs
janee.axion.data_prospect_temp_cv1 (cv1 import)janee.axion.data_prospect_temp_retailscore (retail score import)janee.axion.janee_ari (score → ARI bands)janee.axion.janee_data_applicationjanee.axion.janee_accountstate_logjanee.axion.janee_data_notesjanee.axion.janee_fraud_celljanee.axion.janee_fraud_idtrader.customerstrader.hp_transaction_headerOutput
janee.axion.data_prospect_scoring enriched with:
ScoreDate, Scorecard, padded IDNumber, normalized Cellphoneari (from ARI table)Worst_Delq (derived from HP profiles)ExclusionReason (policy/fraud flags)DELETE FROM `janee.axion`.data_prospect_temp_cv1 WHERE 1=1;
DELETE FROM `janee.axion`.data_prospect_temp_retailscore WHERE 1=1;
/* Import fresh data into the two temp tables */
data_prospect_scoring for todayINSERT IGNORE `janee.axion`.data_prospect_scoring
SELECT
cv1.*,
score.*,
DATE(NOW()) AS ScoreDate,
IF(CAST(PP0001AL AS INT) > 0, 'TU_RI_THICK', 'TU_RI_THIN') AS Scorecard,
'' AS ari,
'' AS JaNeeApplicationDate,
'' AS JaNeeApplicationOutcome,
'' AS JaNeeDeclineReason,
CONCAT('0', RIGHT(Cellnumber, 9)) AS Cellphone,
clientref2 AS StoreID,
'' AS ExclusionReason,
'' AS WorstDelq
FROM `janee.axion`.data_prospect_temp_cv1 AS cv1
JOIN `janee.axion`.data_prospect_temp_retailscore AS score
ON cv1.FillerCode = score.FILLER_CODE;
UPDATE `janee.axion`.data_prospect_scoring
SET IDNumber = LPAD(TRIM(IDNumber), 13, '0')
WHERE IDNumber IS NOT NULL
AND CHAR_LENGTH(TRIM(IDNumber)) < 13;
UPDATE
`janee.axion`.data_prospect_scoring AS a
JOIN `janee.axion`.janee_ari AS c
ON a.scorecard = c.scorecard
AND a.score >= c.start_score
AND a.score <= c.end_score
SET
a.ari = c.ari,
a.Cellphone = CONCAT('0', RIGHT(a.Cellnumber, 9))
WHERE a.ScoreDate = DATE(NOW());
ApplicationDate.step < 999) or falls back to 'Incomplete'.Source = 'Application Declined'.UPDATE
`janee.axion`.data_prospect_scoring AS scoring
LEFT JOIN `janee.axion`.janee_data_application AS jn
ON scoring.idnumber = jn.idnumber
LEFT JOIN `janee.axion`.janee_accountstate_log AS state
ON jn.applicationguid = state.applicationguid
LEFT JOIN `janee.axion`.janee_data_notes AS notes
ON jn.Applicationguid = notes.applicationguid
AND notes.`Source` = 'Application Declined'
SET
scoring.JaNeeApplicationDate = jn.ApplicationDate,
scoring.JaNeeApplicationOutcome= COALESCE(state.state, 'Incomplete'),
scoring.JaNeeDeclineReason = notes.Note_Content
WHERE
scoring.idnumber <> ''
AND ( state.stateguid = (
SELECT stateguid
FROM `janee.axion`.janee_accountstate_log AS lg
WHERE jn.applicationguid = lg.applicationguid
AND step < 999
ORDER BY step DESC
LIMIT 1
)
OR state.stateguid IS NULL )
AND jn.applicationguid = (
SELECT applicationguid
FROM `janee.axion`.janee_data_application AS c
WHERE c.idnumber = scoring.idnumber
ORDER BY Applicationdate DESC
LIMIT 1
)
AND scoring.ScoreDate = DATE(NOW());
Worst_Delq from Trader HP historyhp_payment_profile chars per ID into a single string.J/L/W) over numeric buckets 9→0.UPDATE `janee.axion`.data_prospect_scoring s
JOIN (
SELECT
c.db_idnumber AS id13,
GROUP_CONCAT(t.hp_payment_profile ORDER BY t.hp_id SEPARATOR '') AS worst_del2
FROM trader.customers AS c
JOIN trader.hp_transaction_header AS t
ON c.db_accountcode = t.meta_account
GROUP BY id13
) p
ON p.id13 = s.IDNumber
SET s.Worst_Delq =
CASE
WHEN p.worst_del2 REGEXP '[JLW]' THEN 'WOFF'
WHEN p.worst_del2 REGEXP '9' THEN '9'
WHEN p.worst_del2 REGEXP '8' THEN '8'
WHEN p.worst_del2 REGEXP '7' THEN '7'
WHEN p.worst_del2 REGEXP '6' THEN '6'
WHEN p.worst_del2 REGEXP '5' THEN '5'
WHEN p.worst_del2 REGEXP '4' THEN '4'
WHEN p.worst_del2 REGEXP '3' THEN '3'
WHEN p.worst_del2 REGEXP '2' THEN '2'
WHEN p.worst_del2 REGEXP '1' THEN '1'
WHEN p.worst_del2 REGEXP '0' THEN '0'
ELSE NULL
END
WHERE s.ScoreDate = DATE(NOW());
ExclusionReason (policy & fraud flags)NG3002AL > 0 → Policy Decline – Admin DefaultNG0001AL > 0 → Policy Referral – Adverse DefaultDM0801AL = 'Y' → Policy Decline – Debt CounsellingWorst_Delq = 'WOFF' → Policy Referral – Previous Full House WoffUPDATE
`janee.axion`.data_prospect_scoring AS scoring
LEFT JOIN `janee.axion`.janee_fraud_cell AS cell
ON scoring.cellphone = cell.cellphone
LEFT JOIN `janee.axion`.janee_fraud_id AS id
ON scoring.idnumber = id.idnumber
SET ExclusionReason = CONCAT(
IF(NG3002AL > 0, 'Policy Decline - Admin Default, ', ''),
IF(NG0001AL > 0, 'Policy Referral - AdverseDefault, ', ''),
IF(DM0801AL = 'Y', 'Policy Decline - DebtCounselling, ', ''),
IF(cell.cellphone IS NOT NULL OR id.idnumber IS NOT NULL,
'Policy Referral - Suspected Internal Fraud, ', ''),
IF(Worst_Delq = 'WOFF', 'Policy Referral - Previous Full House Woff, ', '')
)
WHERE scoring.ScoreDate = DATE(NOW());
DELETE FROM `janee.axion`.data_prospect_scoring WHERE ScoreDate = DATE(NOW());
Row count for today
SELECT COUNT(*) FROM `janee.axion`.data_prospect_scoring WHERE ScoreDate = DATE(NOW());
Missing IDs or invalid lengths
SELECT COUNT(*) FROM `janee.axion`.data_prospect_scoring
WHERE ScoreDate = DATE(NOW()) AND (IDNumber IS NULL OR CHAR_LENGTH(IDNumber) <> 13);
Unmapped ARI
SELECT COUNT(*) FROM `janee.axion`.data_prospect_scoring
WHERE ScoreDate = DATE(NOW()) AND (ari IS NULL OR ari = '');
Empty ExclusionReason but should be flagged
SELECT idnumber, cellphone, Worst_Delq, NG3002AL, NG0001AL, DM0801AL
FROM `janee.axion`.data_prospect_scoring
WHERE ScoreDate = DATE(NOW()) AND (ExclusionReason IS NULL OR ExclusionReason = '');
/* 0) Reset temps */
DELETE FROM `janee.axion`.data_prospect_temp_cv1 WHERE 1=1;
DELETE FROM `janee.axion`.data_prospect_temp_retailscore WHERE 1=1;
/* Import both temp tables */
/* 1) Seed scoring */
INSERT IGNORE `janee.axion`.data_prospect_scoring
SELECT
cv1.*,
score.*,
DATE(NOW()) AS ScoreDate,
IF(CAST(PP0001AL AS INT) > 0, 'TU_RI_THICK', 'TU_RI_THIN') AS Scorecard,
'' AS ari,
'' AS JaNeeApplicationDate,
'' AS JaNeeApplicationOutcome,
'' AS JaNeeDeclineReason,
CONCAT('0', RIGHT(Cellnumber, 9)) AS Cellphone,
clientref2 AS StoreID,
'' AS ExclusionReason,
'' AS WorstDelq
FROM `janee.axion`.data_prospect_temp_cv1 AS cv1
JOIN `janee.axion`.data_prospect_temp_retailscore AS score
ON cv1.FillerCode = score.FILLER_CODE;
/* 2) Pad IDs to 13 */
UPDATE `janee.axion`.data_prospect_scoring
SET IDNumber = LPAD(TRIM(IDNumber), 13, '0')
WHERE IDNumber IS NOT NULL
AND CHAR_LENGTH(TRIM(IDNumber)) < 13;
/* 3) ARI + cellphone */
UPDATE
`janee.axion`.data_prospect_scoring AS a
JOIN `janee.axion`.janee_ari AS c
ON a.scorecard = c.scorecard
AND a.score >= c.start_score
AND a.score <= c.end_score
SET
a.ari = c.ari,
a.Cellphone = CONCAT('0', RIGHT(a.Cellnumber, 9))
WHERE a.ScoreDate = DATE(NOW());
/* 4) Latest application details */
UPDATE
`janee.axion`.data_prospect_scoring AS scoring
LEFT JOIN `janee.axion`.janee_data_application AS jn
ON scoring.idnumber = jn.idnumber
LEFT JOIN `janee.axion`.janee_accountstate_log AS state
ON jn.applicationguid = state.applicationguid
LEFT JOIN `janee.axion`.janee_data_notes AS notes
ON jn.Applicationguid = notes.applicationguid
AND notes.`Source` = 'Application Declined'
SET
scoring.JaNeeApplicationDate = jn.ApplicationDate,
scoring.JaNeeApplicationOutcome = COALESCE(state.state,'Incomplete'),
scoring.JaNeeDeclineReason = notes.Note_Content
WHERE
scoring.idnumber <> ''
AND ( state.stateguid = (
SELECT stateguid
FROM `janee.axion`.janee_accountstate_log AS lg
WHERE jn.applicationguid = lg.applicationguid AND step < 999
ORDER BY step DESC LIMIT 1
) OR state.stateguid IS NULL )
AND jn.applicationguid = (
SELECT applicationguid
FROM `janee.axion`.janee_data_application AS c
WHERE c.idnumber = scoring.idnumber
ORDER BY Applicationdate DESC
LIMIT 1
)
AND scoring.ScoreDate = DATE(NOW());
/* 5) Worst_Delq */
UPDATE `janee.axion`.data_prospect_scoring s
JOIN (
SELECT
c.db_idnumber AS id13,
GROUP_CONCAT(t.hp_payment_profile ORDER BY t.hp_id SEPARATOR '') AS worst_del2
FROM trader.customers AS c
JOIN trader.hp_transaction_header AS t
ON c.db_accountcode = t.meta_account
GROUP BY id13
) p
ON p.id13 = s.IDNumber
SET s.Worst_Delq =
CASE
WHEN p.worst_del2 REGEXP '[JLW]' THEN 'WOFF'
WHEN p.worst_del2 REGEXP '9' THEN '9'
WHEN p.worst_del2 REGEXP '8' THEN '8'
WHEN p.worst_del2 REGEXP '7' THEN '7'
WHEN p.worst_del2 REGEXP '6' THEN '6'
WHEN p.worst_del2 REGEXP '5' THEN '5'
WHEN p.worst_del2 REGEXP '4' THEN '4'
WHEN p.worst_del2 REGEXP '3' THEN '3'
WHEN p.worst_del2 REGEXP '2' THEN '2'
WHEN p.worst_del2 REGEXP '1' THEN '1'
WHEN p.worst_del2 REGEXP '0' THEN '0'
ELSE NULL
END
WHERE s.ScoreDate = DATE(NOW());
/* 6) ExclusionReason */
UPDATE
`janee.axion`.data_prospect_scoring AS scoring
LEFT JOIN `janee.axion`.janee_fraud_cell AS cell
ON scoring.cellphone = cell.cellphone
LEFT JOIN `janee.axion`.janee_fraud_id AS id
ON scoring.idnumber = id.idnumber
SET ExclusionReason = CONCAT(
IF(NG3002AL > 0, 'Policy Decline - Admin Default, ', ''),
IF(NG0001AL > 0, 'Policy Referral - AdverseDefault, ', ''),
IF(DM0801AL = 'Y', 'Policy Decline - DebtCounselling, ', ''),
IF(cell.cellphone IS NOT NULL OR id.idnumber IS NOT NULL,
'Policy Referral - Suspected Internal Fraud, ', ''),
IF(Worst_Delq = 'WOFF', 'Policy Referral - Previous Full House Woff, ', '')
)
WHERE scoring.ScoreDate = DATE(NOW());