Crescent Point primary country assignment
Overview
Overview
To admin orgs CP needs Primary country detail field set.
SQL
SQL
Clear existing
Clear existing
DELETE FROM `organization_details`
WHERE detail_field_id = 193;
Setup Best Guess
Setup Best Guess
INSERT INTO organization_details
SELECT
193 /* df */
, o.id
,
CASE
(
(
SELECT COUNT(*)
FROM group_member_assn AS gma
LEFT JOIN groups AS g
ON g.id = gma.group_id
WHERE g.organization_id = o.id
AND g.name = 'Canada'
) >=
(
SELECT COUNT(*)
FROM group_member_assn AS gma
LEFT JOIN groups AS g
ON g.id = gma.group_id
WHERE g.organization_id = o.id
AND g.name = 'USA'
)
) WHEN 1 THEN '95' ELSE '96' END AS BestGuess
FROM
organizations AS o
LEFT JOIN organization_details AS od
ON (od.organization_id = o.id AND od.detail_field_id = 193)
WHERE o.id > 4