Crescent Point primary country assignment

Overview

To admin orgs CP needs Primary country detail field set.

SQL

Clear existing

DELETE FROM `organization_details`

WHERE detail_field_id = 193;

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