Knowledgebase

Logic for Grouping NTEE into ntmaj10, ntmaj12, ntmaj5, and MAJGRPB


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


Added 11/16/2011 by jdurnford, Modified 11/16/2011 by jdurnford

Comments

No comments.

Please login to add your own comments.