***********************************************************************************************************/
SELECT * FROM (     --1,255,458
--has region, not one of the "expand" cases     --151
SELECT  a.person_id PersonId,
        NULL CountryId,
        NULL CountrySubdivisionId,
        d.RegionId,
        1 DesirableIndicator,
        a.ent_oper, a.ent_date, a.chg_oper, a.chg_date, a.rec_sts
FROM questnt.dbo.peo_desired_location a WITH (nolock)
JOIN questnt.dbo.peo_master b WITH (nolock)
    ON a.person_id = b.person_id
JOIN questnt.dbo.cd_region c WITH (nolock)
    ON LTRIM(RTRIM(a.region_code)) = LTRIM(RTRIM(c.region_code))
JOIN q2.dbo.Region d WITH (nolock)
    ON LTRIM(RTRIM(a.region_code)) = d.Code
JOIN q2.dbo.RegionCategory e WITH (nolock)
    ON  d.RegionCategoryId = e.RegionCategoryId
    AND e.Code = 'CR'
WHERE   a.state_code = ''
AND     a.province_code = ''
AND     a.country_code = ''
AND     a.region_code <> ''
AND     c.definition NOT LIKE 'Expand%'

UNION
--continent only    --3896
SELECT  a.person_id PersonId,
        NULL CountryId,
        NULL CountrySubdivisionId,
        d.RegionId,
        1 DesirableIndicator,
        a.ent_oper, a.ent_date, a.chg_oper, a.chg_date, a.rec_sts
FROM questnt.dbo.peo_desired_location a WITH (nolock)
JOIN questnt.dbo.peo_master b WITH (nolock)
    ON a.person_id = b.person_id
LEFT JOIN questnt.dbo.cd_region c WITH (nolock)
    ON LTRIM(RTRIM(a.region_code)) = LTRIM(RTRIM(c.region_code))
    AND c.definition LIKE 'Expand%'
JOIN q2.dbo.Region d WITH (nolock)
    ON LTRIM(RTRIM(a.continent_code)) = d.Code
JOIN q2.dbo.RegionCategory e WITH (nolock)
    ON  d.RegionCategoryId = e.RegionCategoryId
    AND e.Code = 'CO'
WHERE   a.state_code = ''
AND     a.province_code = ''
AND     a.country_code = ''
AND     ((a.region_code <> '' AND c.region_code IS NOT NULL) OR a.region_code = '')
AND     a.continent_code <> ''

UNION
--worldwide only    --152
SELECT  a.person_id PersonId,
        c.CountryId,
        NULL CountrySubdivisionId,
        e.RegionId,
        1 DesirableIndicator,
        a.ent_oper, a.ent_date, a.chg_oper, a.chg_date, a.rec_sts
FROM questnt.dbo.peo_desired_location a WITH (nolock)
JOIN questnt.dbo.peo_master b WITH (nolock)
    ON a.person_id = b.person_id
LEFT JOIN q2.dbo.Country c WITH (nolock)
    ON LTRIM(RTRIM(a.country_code)) = c.Code
JOIN q2.dbo.RegionCategory d WITH (nolock)
    ON d.Code = 'WW'
JOIN q2.dbo.Region e WITH (nolock)
    ON LTRIM(RTRIM(a.continent_code)) = e.Code
    AND e.RegionCategoryId = d.RegionCategoryId
WHERE   a.continent_code = 'WW'

UNION
--country only  --1,229,169
SELECT  a.person_id PersonId,
        c.CountryId,
        NULL CountrySubdivisionId,
        NULL RegionId,
        1 DesirableIndicator,
        a.ent_oper, a.ent_date, a.chg_oper, a.chg_date, a.rec_sts
FROM questnt.dbo.peo_desired_location a WITH (nolock)
JOIN questnt.dbo.peo_master b WITH (nolock)
    ON a.person_id = b.person_id
LEFT JOIN q2.dbo.Country c WITH (nolock)
    ON LTRIM(RTRIM(a.country_code)) = c.Code
WHERE   LTRIM(RTRIM(a.country_code)) <> ''
AND     LTRIM(RTRIM(a.province_code)) = ''
AND     LTRIM(RTRIM(a.state_code)) = ''

UNION
--province only     --335
SELECT  a.person_id PersonId,
        c.CountryId,
        e.CountrySubdivisionId,
        NULL RegionId,
        1 DesirableIndicator,
        a.ent_oper, a.ent_date, a.chg_oper, a.chg_date, a.rec_sts
FROM questnt.dbo.peo_desired_location a WITH (nolock)
JOIN questnt.dbo.peo_master b WITH (nolock)
    ON a.person_id = b.person_id
LEFT JOIN q2.dbo.Country c WITH (nolock)
    ON LTRIM(RTRIM(a.country_code)) = c.Code
LEFT JOIN q2.dbo.CountrySubdivision e WITH (nolock)
    ON 'P_' + LTRIM(RTRIM(a.province_code)) = e.Code
WHERE   LTRIM(RTRIM(a.country_code)) <> ''
AND     LTRIM(RTRIM(a.province_code)) <> ''
AND     LTRIM(RTRIM(a.state_code)) = ''

UNION
--state only        --1559
SELECT  a.person_id PersonId,
        c.CountryId,
        e.CountrySubdivisionId,
        NULL RegionId,
        1 DesirableIndicator,
        a.ent_oper, a.ent_date, a.chg_oper, a.chg_date, a.rec_sts
FROM questnt.dbo.peo_desired_location a WITH (nolock)
JOIN questnt.dbo.peo_master b WITH (nolock)
    ON a.person_id = b.person_id
LEFT JOIN q2.dbo.Country c WITH (nolock)
    ON LTRIM(RTRIM(a.country_code)) = c.Code
LEFT JOIN q2.dbo.CountrySubdivision e WITH (nolock)
    ON 'S_' + LTRIM(RTRIM(a.state_code)) = e.Code
WHERE   LTRIM(RTRIM(a.country_code)) <> ''
AND     LTRIM(RTRIM(a.province_code)) = ''
AND     LTRIM(RTRIM(a.state_code)) <> ''
) un