This is the MySQL code used for grouping 3-character NTEE codes into ntmaj12, ntmaj10, ntmaj5 and MAJGRPB categories. The testing conditions within MySQL CASE statements are listed in descending order of priority, so when the highest condition in the list is met then all subsequent lower conditions are ignored. The CASE statements themselves are listed in priority as well, so ntmaj10 is created first, followed by ntmaj12 and ntmaj5. Creation of MAJGRPB is independent from others.
The definitions of all code values are found in the data dictionary of any BMF or Core PC table on the DataWeb.
ntmaj10 = /* ntmaj10 is the basis for creating ntmaj12 and ntmaj5 */
CASE
WHEN SUBSTRING(NTEECC,1,1) = 'A' THEN 'AR'
WHEN SUBSTRING(NTEECC,1,1) = 'B' THEN 'ED'
WHEN SUBSTRING(NTEECC,1,1) IN ('C','D') THEN 'EN'
WHEN SUBSTRING(NTEECC,1,1) IN ('E','F','G','H') THEN 'HE'
WHEN SUBSTRING(NTEECC,1,1) IN ('I','J','K','L','M','N','O','P') THEN 'HU'
WHEN SUBSTRING(NTEECC,1,1) IN ('Q') THEN 'IN'
WHEN SUBSTRING(NTEECC,1,1) IN ('R','S','T','U','V','W') THEN 'PU'
WHEN SUBSTRING(NTEECC,1,1) IN ('X') THEN 'RE'
WHEN SUBSTRING(NTEECC,1,1) IN ('Y') THEN 'MU'
WHEN SUBSTRING(NTEECC,1,1) IN ('Z',' ') THEN 'UN'
else 'UN'
END
ntmaj12 = /* takes ntmaj10 and identifies 'Higher Ed' separate from other B's, and Hospitals separate from other E's */
CASE
WHEN SUBSTRING(NTEECC,1,2) IN ('B4','B5') THEN 'BH'
WHEN SUBSTRING(NTEECC,1,2) = 'E2' THEN 'EH'
else ntmaj10
END
ntmaj5 = /* lumps 6 categories of ntmaj10 into 1 (All Others) */
CASE
WHEN ntmaj10 in ('IN','EN','PU','RE','MU','UN') THEN 'OT'
ELSE ntmaj10
END
MAJGRPB = /* first character of NTEE (A-Z), with 'Higher Ed' separate from other B's, and Hospitals separate from other E's (28 total) */
CASE
WHEN SUBSTRING(NTEECC,1,2) IN ('B4','B5') THEN 'BH'
WHEN SUBSTRING(NTEECC,1,2) ='E2' THEN 'EH'
ELSE SUBSTRING(NTEECC,1,1)
END