code.sql

WITH ethnicities AS (
          SELECT
            Patient_ID,
            date,
            ethnicity_code,
            ROW_NUMBER() OVER (
                PARTITION BY Patient_ID ORDER BY date DESC
            ) AS row_num
          FROM (
            SELECT
              'apcs' AS type,
              Patient_ID,
              Discharge_Date AS date,
              Ethnic_group AS ethnicity_code
            FROM
              APCS
            UNION ALL
            SELECT
              'ec' AS type,
              Patient_ID,
              Arrival_Date AS date,
              Ethnic_Category AS ethnicity_code
            FROM
              EC
            UNION ALL
            SELECT
              'opa' AS type,
              Patient_ID,
              Appointment_Date AS date,
              Ethnic_Category AS ethnicity_code
            FROM
              OPA
          ) t
          WHERE ethnicity_code IS NOT NULL
            AND ethnicity_code != '99'
            AND CHARINDEX('Z', ethnicity_code) != 1
        )
        SELECT
          Patient_ID,
          date,
          ethnicity_code,
          ethnicity_code AS code
        FROM ethnicities
        WHERE row_num = 1;

group_16.sql

        WITH ethnicities AS (
          SELECT
            Patient_ID,
            date,
            ethnicity_code,
            ROW_NUMBER() OVER (
                PARTITION BY Patient_ID ORDER BY date DESC
            ) AS row_num
          FROM (
            SELECT
              'apcs' AS type,
              Patient_ID,
              Discharge_Date AS date,
              Ethnic_group AS ethnicity_code
            FROM
              APCS
            UNION ALL
            SELECT
              'ec' AS type,
              Patient_ID,
              Arrival_Date AS date,
              Ethnic_Category AS ethnicity_code
            FROM
              EC
            UNION ALL
            SELECT
              'opa' AS type,
              Patient_ID,
              Appointment_Date AS date,
              Ethnic_Category AS ethnicity_code
            FROM
              OPA
          ) t
          WHERE ethnicity_code IS NOT NULL
            AND ethnicity_code != '99'
            AND CHARINDEX('Z', ethnicity_code) != 1
        )
        SELECT
          Patient_ID,
          date,
          ethnicity_code,

            CASE
                WHEN ethnicity_code LIKE 'A%' THEN 1
WHEN ethnicity_code LIKE 'B%' THEN 2
WHEN ethnicity_code LIKE 'C%' THEN 3
WHEN ethnicity_code LIKE 'D%' THEN 4
WHEN ethnicity_code LIKE 'E%' THEN 5
WHEN ethnicity_code LIKE 'F%' THEN 6
WHEN ethnicity_code LIKE 'G%' THEN 7
WHEN ethnicity_code LIKE 'H%' THEN 8
WHEN ethnicity_code LIKE 'J%' THEN 9
WHEN ethnicity_code LIKE 'K%' THEN 10
WHEN ethnicity_code LIKE 'L%' THEN 11
WHEN ethnicity_code LIKE 'M%' THEN 12
WHEN ethnicity_code LIKE 'N%' THEN 13
WHEN ethnicity_code LIKE 'P%' THEN 14
WHEN ethnicity_code LIKE 'R%' THEN 15
WHEN ethnicity_code LIKE 'S%' THEN 16
                ELSE 0
            END
             AS group_16
        FROM ethnicities
        WHERE row_num = 1;

group_6.sql

        WITH ethnicities AS (
          SELECT
            Patient_ID,
            date,
            ethnicity_code,
            ROW_NUMBER() OVER (
                PARTITION BY Patient_ID ORDER BY date DESC
            ) AS row_num
          FROM (
            SELECT
              'apcs' AS type,
              Patient_ID,
              Discharge_Date AS date,
              Ethnic_group AS ethnicity_code
            FROM
              APCS
            UNION ALL
            SELECT
              'ec' AS type,
              Patient_ID,
              Arrival_Date AS date,
              Ethnic_Category AS ethnicity_code
            FROM
              EC
            UNION ALL
            SELECT
              'opa' AS type,
              Patient_ID,
              Appointment_Date AS date,
              Ethnic_Category AS ethnicity_code
            FROM
              OPA
          ) t
          WHERE ethnicity_code IS NOT NULL
            AND ethnicity_code != '99'
            AND CHARINDEX('Z', ethnicity_code) != 1
        )
        SELECT
          Patient_ID,
          date,
          ethnicity_code,

            CASE
                WHEN ethnicity_code LIKE 'A%' THEN 1
WHEN ethnicity_code LIKE 'B%' THEN 1
WHEN ethnicity_code LIKE 'C%' THEN 1
WHEN ethnicity_code LIKE 'D%' THEN 2
WHEN ethnicity_code LIKE 'E%' THEN 2
WHEN ethnicity_code LIKE 'F%' THEN 2
WHEN ethnicity_code LIKE 'G%' THEN 2
WHEN ethnicity_code LIKE 'H%' THEN 3
WHEN ethnicity_code LIKE 'J%' THEN 3
WHEN ethnicity_code LIKE 'K%' THEN 3
WHEN ethnicity_code LIKE 'L%' THEN 3
WHEN ethnicity_code LIKE 'M%' THEN 4
WHEN ethnicity_code LIKE 'N%' THEN 4
WHEN ethnicity_code LIKE 'P%' THEN 4
WHEN ethnicity_code LIKE 'R%' THEN 5
WHEN ethnicity_code LIKE 'S%' THEN 5
                ELSE 0
            END
             AS group_6
        FROM ethnicities
        WHERE row_num = 1;