HH Magnet Table preparation to be run prior to running reports

4/27/2015 - 2:26 PM
HH Magnet Table preparation to be run prior to running reports

/* DATE: April 27th, 2015
 * AUTH: James R. Aylesworth
 * DESC: Remove, insert, and clean data for HH Magnet Reporting
*/

--delete all secondary appointments and < .1FTE folks ( < 4hrs/wk)
DELETE
  (SELECT EMPLOYEE_ID ,
    FIRST_NAME ,
    LAST_NAME ,
    MIN_WKLY_HRS ,
    SMH_STRT_DT ,
    POS_CODE ,
    TITLE ,
    DEPT ,
    SMH_TERM_DT ,
    UNIT_ID
  FROM MAGNET_PROFILES_HH
  WHERE (POS_CODE = 2
  OR MIN_WKLY_HRS < 4)
  );
  
--update empl unit_id's
UPDATE
  (SELECT EMPLOYEE_ID ,
    FIRST_NAME ,
    LAST_NAME ,
    MIN_WKLY_HRS ,
    SMH_STRT_DT ,
    POS_CODE ,
    TITLE ,
    DEPT ,
    SMH_TERM_DT ,
    UNIT_ID
  FROM MAGNET_PROFILES_HH
  )
SET UNIT_ID = DEPT;

--remove position codes from 4/23/15 email from Laurie Ernest
DELETE
FROM MAGNET_PROFILES_HH
WHERE POS_CODE IN (136,137,138,139,144,145,146,152,174,187,201,202,207,208,210, 224,237,239,254,417,474,543,549,714,722,723,817,829,1004,1034,1062,1199,1206, 1220,1798,2210,2240,2303,2309);

--remove employee ids based on spreadhsheet Laurie Sent 4/27/15
DELETE
FROM MAGNET_PROFILES_HH
WHERE EMPLOYEE_ID IN (19769,19748,19816,19678,14190,19737,17611,19352,13532,16760,18609,18612,18984,16391,16269,18152,18203,19307,18201,18460,19588,19840,17752,18871,9734,15365,18807,13684,15507,17749,18339,19399,19848,19277,19632,19176,19321,19367,18434,8234,19637,13528,19651,19286,15576,18002,17967,4756,2005,19572,19428,19212,13287,17551,19211,19639,18658,10470,19198,19436,9453,19898,10717,17846,19824,14024,19063,13805,16489,19235,19133,17392,19740,19768,18931,17581,12772,18679,19844,9577,19132,18811,19244,19703,15547,17503,19157,18717,19322,18691,14085,17418,18426,12886,17827,18916,19473,19391,16742,15521,1455,10638,19849,16313,18690,18960,18662,14659,16932,12284,19044,18878,5166,13970,19855,11488,12557,19695,11418,18821,19711,19680,19442,8278,17321,19090,15591,3376,16991,18454,18735,15198,13730,12266,40016,12909,12629,14720,19074,19224,19236,19053,11058,2669,1524,14893,18353,17812,13543,15661,17236,17587,16527,15632,18220,19178,19640,19159,19339,19111,19677,19785,6703,19480,19821,19772,19287,19519,19329,16013,17068,6572,18020,19186,17273,19675,11284,19179,6573,19316,19363,14701,19693,19425,18820,19805,3439,18247,17291,18943,15372,14162,18823,19267,16934,2064,17801,19579,18440,18938,18398,19858,19502,18096,1643,19026,19642,16082,19810,1346,14734,15041,5494,14482,18551,18964,3527,10910,12988,18847,17305,18154,2148,14479,7972,7599,17976,19807,19540,18595,9193,5829,6624,5423,12696,18707,17566,14490,19060,2968,18111,1703,18365,18187,5163,19318,17921,18557,15907,18004,17632,7878,2016,18886,16927,16843,18711,19741,11944,17105,11581,11287,16274,16016,16824,40082,18525,17385,15222,14616,19605,19117,19445,18123,19129,19232,19825,19846,19289,18291,19291,18446,19283,18296,17764,17756,18122,18995,19833,19491,18141,18876,18992,19828,17757,18990,19290,18882,18373,18809,18757,19048,17955,14403,1690,17816,12986,19683,15575,18403,15568,18349,13981,17286,18654,14874,8378,9151,18747,17992,19160,18634,19245,17810,19062,17940,1723,17831,2704,6187,6657,19630,19767,19512,19551,19554,19899,13758,15446,13261,2837,18307,14654,19497,1613,3702,3651,13239,14470,16543,19671,17979,10643,16756,18889,19842,17352,2056,18970,13958,7784,3079,16105,5925,8169,17314,19847,7734,19326,18422,19208,19679,10280,19573,19458,9064,16189,19567,19569,19414,17950,18041,14537,19043,18129,17667,18861,18036,18887,19274,18896,19306,19699,8396,16441,16150,19634,18265,18794,14649,18703,3855,18026,19017,7921,17089,3822,12914,13093,16442,19529,17753,13334,16974,19601,19169,8132,18608,7166,17971,19531,11475,11427,13989,19460,19075,14702,19334,14838,19530,14258,18705,18086,19156,18913,10595,13567,10727,10613,17703,18742,16490,19319,15107,17444,16051,12623,15132,15725,19047,18642,3207); -- 452 rows deleted

--display any duplicate profiles (removed undesired ones)
SELECT employee_id
FROM MAGNET_PROFILES_HH
GROUP BY employee_id
HAVING COUNT(*) > 1;

--count distinct entries
SELECT COUNT(DISTINCT EMPLOYEE_ID)
FROM MAGNET_PROFILES_HH; --1242 rows returned

/*===================== PREPARE TABLES FOR REPORTING =========================*/
--// CERTIFICATIONS
TRUNCATE TABLE IER_CERTIFICATIONS_HH;
INSERT
INTO IER_CERTIFICATIONS_HH
  (SELECT EMPLOYEE_ID ,
      CERTIFICATION ,
      ROW_ID ,
      EXP_DATE ,
      CERT_TITLE ,
      CERT_BODY ,
      DATE_MODIFIED ,
      FACILITY
    FROM IER_CERTIFICATIONS
    WHERE EMPLOYEE_ID IN
      ( SELECT DISTINCT employee_id FROM MAGNET_PROFILES_HH
      )
  ); -- 926 rows inserted
DELETE
FROM IER_CERTIFICATIONS_HH
WHERE CERT_TITLE LIKE 'I have nothing to enter%'
OR ( CERT_TITLE LIKE 'Verified%by%'
AND CERT_BODY = 'NA'); -- 638 rows deleted

--// COMMUNITY
TRUNCATE TABLE IER_COMMUNITY_HH;
INSERT
INTO IER_COMMUNITY_HH
  (SELECT START_DATE ,
      ORG_NAME ,
      MY_ROLE ,
      EMPLOYEE_ID ,
      EVENT_NAME ,
      ROW_ID ,
      DATE_MODIFIED ,
      END_DATE ,
      FACILITY
    FROM IER_COMMUNITY
    WHERE EMPLOYEE_ID IN
      ( SELECT DISTINCT employee_id FROM MAGNET_PROFILES_HH
      )
  ); -- 854 rows inserted
DELETE
FROM IER_COMMUNITY_HH
WHERE ORG_NAME LIKE 'I have nothing to enter%'
OR ( ORG_NAME LIKE 'Verified%by%'
AND EVENT_NAME = 'NA'); -- 523 rows deleted

--// COURSES
TRUNCATE TABLE IER_COURSES_HH;
INSERT
INTO IER_COURSES_HH
  (SELECT COURSE ,
      COLLEGE ,
      END_DATE ,
      ROLE ,
      EMPLOYEE_ID ,
      HOURS ,
      ROW_ID ,
      DATE_MODIFIED ,
      FACILITY
    FROM IER_COURSES
    WHERE EMPLOYEE_ID IN
      ( SELECT DISTINCT employee_id FROM MAGNET_PROFILES_HH
      )
  ); -- 942 rows inserted
DELETE
FROM IER_COURSES_HH
WHERE COLLEGE LIKE 'I have nothing to enter%'
OR ( COLLEGE LIKE 'Verified%by%'
AND ROLE = 'NA'); -- 622 rows deleted

--// DEGREE
TRUNCATE TABLE IER_DEGREE_HH;
INSERT
INTO IER_DEGREE_HH
  (SELECT EMPLOYEE_ID ,
      DEGREE_TYPE ,
      NURSING ,
      MAJOR ,
      GRAD_YEAR ,
      COLLEGE ,
      ROW_ID ,
      DATE_MODIFIED ,
      FACILITY
    FROM IER_DEGREE
    WHERE EMPLOYEE_ID IN
      ( SELECT DISTINCT employee_id FROM MAGNET_PROFILES_HH
      )
  ); -- 1,069 rows inserted
DELETE
FROM IER_DEGREE_HH
WHERE GRAD_YEAR < 2015
AND DEGREE_TYPE LIKE 'Pursuing%'
OR ( DEGREE_TYPE = 'No College Degree'
AND MAJOR        = 'Not Applicable' ); -- 22 rows deleted

--// HONORS
TRUNCATE TABLE IER_HONORS_HH;
INSERT
INTO IER_HONORS_HH
  (SELECT AWARD ,
      ORGANIZATION ,
      AWARD_MONTH ,
      EMPLOYEE_ID ,
      ROW_ID ,
      DATE_MODIFIED ,
      FACILITY ,
      AWARD_YEAR
    FROM IER_HONORS
    WHERE EMPLOYEE_ID IN
      ( SELECT DISTINCT employee_id FROM MAGNET_PROFILES_HH
      )
  ); -- 886 rows inserted
DELETE
FROM IER_HONORS_HH
WHERE AWARD LIKE 'I have nothing to enter%'
OR ( AWARD LIKE 'Verified%by%'
AND ORGANIZATION = 'NA'); -- 604 rows deleted

--// INSERVICE
TRUNCATE TABLE IER_INSERVICE_HH;
INSERT
INTO IER_INSERVICE_HH
  (SELECT TITLE ,
      SETTING ,
      DURATION ,
      ROLE ,
      IS_DATE ,
      CONTACT_HRS ,
      EMPLOYEE_ID ,
      ROW_ID ,
      DATE_MODIFIED ,
      CONTACT ,
      FACILITY
    FROM IER_INSERVICE
    WHERE EMPLOYEE_ID IN
      ( SELECT DISTINCT employee_id FROM MAGNET_PROFILES_HH
      )
  ); -- 4,159 rows inserted
DELETE
FROM IER_INSERVICE_HH
WHERE TITLE LIKE 'I have nothing to enter%'
OR ( TITLE LIKE 'Verified%by%'
AND ROLE = 'NA'); -- 359 rows deleted

--// LIFE_CERTS
TRUNCATE TABLE IER_LIFE_CERTS_HH;
INSERT
INTO IER_LIFE_CERTS_HH
  (SELECT EMPLOYEE_ID ,
      CERT_CODE ,
      EXP_DATE ,
      ROLE ,
      ROW_ID ,
      DATE_MODIFIED ,
      FACILITY
    FROM IER_LIFE_CERTS
    WHERE EMPLOYEE_ID IN
      ( SELECT DISTINCT employee_id FROM MAGNET_PROFILES_HH
      )
  ); -- 1,364 rows inserted
DELETE
FROM IER_LIFE_CERTS_HH
WHERE CERT_CODE LIKE 'I have nothing to enter%'
OR ( CERT_CODE LIKE 'Verified%by%'
AND ROLE = 'NA'); -- 226 rows deleted

--// MAGNET_CERT
TRUNCATE TABLE IER_MAGNET_CERT_HH;
INSERT
INTO IER_MAGNET_CERT_HH
  ( SELECT CREDENTIAL, CRED_NAME, CRED_BODY FROM IER_MAGNET_CERT
  ); -- 383 rows inserted
DELETE
FROM IER_MAGNET_CERT_HH
WHERE CREDENTIAL NOT IN
  ( SELECT DISTINCT CERTIFICATION FROM IER_CERTIFICATIONS_HH
  ); -- 294 rows deleted

--// ORGANIZATIONS
TRUNCATE TABLE IER_ORGANIZATIONS_HH;
INSERT
INTO IER_ORGANIZATIONS_HH
  (SELECT POSITION ,
      START_DATE ,
      ORG_NAME ,
      EMPLOYEE_ID ,
      ROW_ID ,
      DATE_MODIFIED ,
      END_DATE ,
      FACILITY ,
      SETTING
    FROM IER_ORGANIZATIONS
    WHERE EMPLOYEE_ID IN
      ( SELECT DISTINCT employee_id FROM MAGNET_PROFILES_HH
      )
  ); -- 923 rows inserted
DELETE
FROM IER_ORGANIZATIONS_HH
WHERE ORG_NAME LIKE 'I have nothing to enter%'
OR ( ORG_NAME LIKE 'Verified%by%'
AND POSITION = 'NA'); -- 498 rows deleted

--//PRESENTATIONS
TRUNCATE TABLE IER_PRESENTATIONS_HH;
INSERT
INTO IER_PRESENTATIONS_HH
  (SELECT EMPLOYEE_ID ,
      ROW_ID ,
      DATE_MODIFIED ,
      AUTHORS ,
      TITLE ,
      CONFERENCE ,
      CITY ,
      STATE ,
      SETTING ,
      PRES_DATE ,
      PRES_TYPE ,
      FACILITY
    FROM IER_PRESENTATIONS
    WHERE EMPLOYEE_ID IN
      ( SELECT DISTINCT employee_id FROM MAGNET_PROFILES_HH
      )
  ); -- 756 rows inserted
DELETE
FROM IER_PRESENTATIONS_HH
WHERE TITLE LIKE 'I have nothing to enter%'
OR ( TITLE LIKE 'Verified%by%'
AND AUTHORS = 'NA'); -- 629 rows deleted

--//PUBLICATIONS
TRUNCATE TABLE IER_PUBLICATIONS_HH;
INSERT
INTO IER_PUBLICATIONS_HH
  (SELECT EMPLOYEE_ID ,
      ROW_ID ,
      DATE_MODIFIED ,
      PUB_DATE ,
      AUTHORS ,
      TITLE ,
      PUB_NAME ,
      PG_NUMBERS ,
      LINK ,
      PUB_TYPE ,
      CH_TITLE ,
      EDITOR ,
      PUBHOUSE_NAME ,
      PUBHOUSE_CITY ,
      PUBHOUSE_STATE ,
      ISSUE_NUMBER ,
      FACILITY
    FROM IER_PUBLICATIONS
    WHERE EMPLOYEE_ID IN
      ( SELECT DISTINCT employee_id FROM MAGNET_PROFILES_HH
      )
  ); -- 651 rows inserted
DELETE
FROM IER_PUBLICATIONS_HH
WHERE TITLE LIKE 'I have nothing to enter%'
OR ( TITLE LIKE 'Verified%by%'
AND AUTHORS = 'NA'); -- 622 rows deleted

--//TEAMS
TRUNCATE TABLE IER_TEAMS_HH;
INSERT
INTO IER_TEAMS_HH
  (SELECT EMPLOYEE_ID ,
      TEAM_NAME ,
      MY_ROLE ,
      ROW_ID ,
      DATE_MODIFIED ,
      ACTIVE_YEAR ,
      FACILITY ,
      SETTING
    FROM IER_TEAMS
    WHERE EMPLOYEE_ID IN
      ( SELECT DISTINCT employee_id FROM MAGNET_PROFILES_HH
      )
  ); -- 1,388 rows inserted
DELETE
FROM IER_TEAMS_HH
WHERE TEAM_NAME LIKE 'I have nothing to enter%'
OR ( TEAM_NAME LIKE 'Verified%by%'
AND MY_ROLE = 'NA'); -- 423 rows deleted

/*========================== FIND ANY MISSING MAPPINGS =======================*/
--find any position codes that are not currently mapped
SELECT DISTINCT POS_CODE, TITLE
FROM MAGNET_PROFILES_HH
WHERE POS_CODE NOT IN (
  SELECT DISTINCT JOB_CODE FROM MAGNET_POS_CODES_HH
);

--find any depts that are not currently mapped
SELECT DISTINCT DEPT FROM MAGNET_PROFILES_HH
WHERE DEPT NOT IN (
  SELECT DISTINCT HRMS_DEPT FROM MAGNET_DIF_UNITS_HH
);
Cacher is the code snippet organizer for pro developers

We empower you and your team to get more done, faster

HH Magnet Table preparation to be run prior to running reports
