Skip to content

Instantly share code, notes, and snippets.

@ghickman
Created Feb 26, 2021
Embed
What would you like to do?
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;
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;
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment