SELECT * FROM (     --1,234,219     --1,235,100
--no country, no region --4,037
SELECT  a.person_id PersonId,
        NULL CountryId,
        NULL CountrySubdivisionId,
        CASE    WHEN LTRIM(RTRIM(a.region_code)) <> '' THEN f.RegionId
                ELSE d.RegionId END 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.RegionCategory c WITH (nolock)
    ON c.Code = 'CO'
LEFT JOIN q2.dbo.Region d WITH (nolock)
    ON LTRIM(RTRIM(a.continent_code)) = d.Code
LEFT JOIN q2.dbo.RegionCategory e WITH (nolock)
    ON e.Code = 'CR'
LEFT JOIN q2.dbo.Region f WITH (nolock)
    ON LTRIM(RTRIM(a.region_code)) = f.Code
LEFT JOIN questnt.dbo.cd_region g
    ON a.region_code = g.region_code
WHERE   a.state_code = ''
AND     a.province_code = ''
AND     a.country_code = ''
AND     ((a.region_code <> '' AND g.definition LIKE 'Expand%') OR a.region_code = '')
AND     a.continent_code <> ''

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