Meaningful Use Project

The final datatable is comprised of three sets of comma-delimited data retrieved from the medical practice's electronic records auditing system.
The first and second set of data can be seen here:

From the first dataset we will be selecting columns: APPTDATE, PATIENT (as FIRSTNAME and LASTNAME), INVOICE and INS (divided at the "|" mark into PRIMARYINS and SECONDARYINS).
From the second data set we will select the SERVICECODES (divided at the "|" mark into CPT1, CPT2, CPT3 and CPT4).

/*Create single table out of Invoice Table and Custom Invoice table*/
CREATE TABLE MEANINGFULEXAMPLE AS 
(
SELECT
  I.APPTDATE,
  SUBSTR(I.PATIENT, 1, (INSTR(I.PATIENT,',') - 1)) AS LASTNAME,
  REGEXP_SUBSTR(I.PATIENT,'[^ ]+',1,2) AS FIRSTNAME,
  I.INVOICE,
  SUBSTR(I.INS, 1, CASE INSTR(I.INS,'|')
            WHEN 0
                THEN LENGTH(I.INS)
            ELSE INSTR(I.INS,'|') - 1
            END) AS PRIMARYINS,
  SUBSTR(I.INS, CASE INSTR(I.INS,'|')
            WHEN 0
                THEN LENGTH(I.INS) + 1
            ELSE INSTR(I.INS,'|') + 1
            END, LENGTH(I.INS) + 1) AS SECONDARYINS,
  REGEXP_SUBSTR(C.SERVICECODES,'[^|]+') AS CPT1,
  REGEXP_SUBSTR(C.SERVICECODES,'[^|]+',1,2) AS CPT2,
  REGEXP_SUBSTR(C.SERVICECODES,'[^|]+',1,3) AS CPT3,
  REGEXP_SUBSTR(C.SERVICECODES,'[^|]+',1,4) AS CPT4
FROM INVOICEKAUFMANEXAMPLE I JOIN CUSTOMINVOICEEXAMPLE C
  ON I.INVOICE = C.INVOICE
)
ORDER BY LASTNAME, FIRSTNAME;

From the third data set we take BIRTHDAY, joining on both FIRSTNAME and LASTNAME.

ALTER TABLE MEANINGFULEXAMPLE
  ADD DOB VARCHAR2(26);
 
UPDATE MEANINGFULEXAMPLE M
SET DOB = ( SELECT C.BIRTHDAY
            FROM CORRESPONDENCEEXAMPLE C
            WHERE ((M.LASTNAME = C.LAST) AND (M.FIRSTNAME = C.FIRST)));

Next comes a good deal of data manipulation that further lowers the amount of time a person must manually sift through the data: we remove all unnecessary codes and insurances that Medicare does not want to receive in the data, we realign the columns to pull the null cells towards the end of the row and we delete all rows empty of codes or insurance.

/*ADD ROWNUMBER COLUMN TO EACH ROW TO CATALOG THE DATA (THIS WILL BE USED LATER WHEN RUNNING THROUGH ROW BY ROW)*/
ALTER TABLE MEANINGFULEXAMPLE
 ADD ROWNUMBER NUMBER(8,0);
 
UPDATE MEANINGFULEXAMPLE
 SET ROWNUMBER = ROWNUM;

/* GO THROUGH CPT CODES AND SET ALL NON-MEDICAL CODES TO NULL*/
UPDATE MEANINGFULEXAMPLE
SET CPT1 = NULL
WHERE NOT (CPT1 = '92004' OR CPT1 = '92014' OR CPT1 = '92002' OR CPT1 = '92012' OR CPT1 = '99211' OR CPT1 = '99202' OR
CPT1 = '99212' OR CPT1 = '99203' OR CPT1 = '99204' OR CPT1 = '99214' OR CPT1 = '92071' OR CPT1 = '68761' OR
CPT1 = '67820' OR CPT1 = '67938' OR CPT1 = '92020' OR CPT1 = '99999' OR CPT1 = '92250' OR CPT1 = '68801' OR
CPT1 = '92015' OR CPT1 = '92083' OR CPT1 = '66984' OR CPT1 = '76514' OR CPT1 = '92133' OR CPT1 = '92134' OR
CPT1 = '92310' OR CPT1 = 'A4262' OR CPT1 = '66982');

UPDATE MEANINGFULEXAMPLE
SET CPT2 = NULL
WHERE NOT (CPT2 = '92004' OR CPT2 = '92014' OR CPT2 = '92002' OR CPT2 = '92012' OR CPT2 = '99211' OR CPT2 = '99202' OR
CPT2 = '99212' OR CPT2 = '99203' OR CPT2 = '99204' OR CPT2 = '99214' OR CPT2 = '92071' OR CPT2 = '68761' OR
CPT2 = '67820' OR CPT2 = '67938' OR CPT2 = '92020' OR CPT2 = '99999' OR CPT2 = '92250' OR CPT2 = '68801' OR
CPT2 = '92015' OR CPT2 = '92083' OR CPT2 = '66984' OR CPT2 = '76514' OR CPT2 = '92133' OR CPT2 = '92134' OR
CPT2 = '92310' OR CPT2 = 'A4262' OR CPT2 = '66982');

UPDATE MEANINGFULEXAMPLE
SET CPT3 = NULL
WHERE NOT (CPT3 = '92004' OR CPT3 = '92014' OR CPT3 = '92002' OR CPT3 = '92012' OR CPT3 = '99211' OR CPT3 = '99202' OR
CPT3 = '99212' OR CPT3 = '99203' OR CPT3 = '99204' OR CPT3 = '99214' OR CPT3 = '92071' OR CPT3 = '68761' OR
CPT3 = '67820' OR CPT3 = '67938' OR CPT3 = '92020' OR CPT3 = '99999' OR CPT3 = '92250' OR CPT3 = '68801' OR
CPT3 = '92015' OR CPT3 = '92083' OR CPT3 = '66984' OR CPT3 = '76514' OR CPT3 = '92133' OR CPT3 = '92134' OR
CPT3 = '92310' OR CPT3 = 'A4262' OR CPT3 = '66982');

UPDATE MEANINGFULEXAMPLE
SET CPT4 = NULL
WHERE NOT (CPT4 = '92004' OR CPT4 = '92014' OR CPT4 = '92002' OR CPT4 = '92012' OR CPT4 = '99211' OR CPT4 = '99202' OR
CPT4 = '99212' OR CPT4 = '99203' OR CPT4 = '99204' OR CPT4 = '99214' OR CPT4 = '92071' OR CPT4 = '68761' OR
CPT4 = '67820' OR CPT4 = '67938' OR CPT4 = '92020' OR CPT4 = '99999' OR CPT4 = '92250' OR CPT4 = '68801' OR
CPT4 = '92015' OR CPT4 = '92083' OR CPT4 = '66984' OR CPT4 = '76514' OR CPT4 = '92133' OR CPT4 = '92134' OR
CPT4 = '92310' OR CPT4 = 'A4262' OR CPT4 = '66982');

/*GO THROUGH ALL OF THE INSURANCE AND SET ALL EYE INSURANCE TO NULL*/
UPDATE MEANINGFULEXAMPLE
SET PRIMARYINS = NULL
WHERE PRIMARYINS = 'EYEMED' OR  PRIMARYINS = 'Vision Service Plan' OR PRIMARYINS = 'Spectera' OR  PRIMARYINS = 'SUPERIOR VISION';

UPDATE MEANINGFULEXAMPLE
SET SECONDARYINS = NULL
WHERE SECONDARYINS = 'EYEMED' OR  SECONDARYINS = 'Vision Service Plan' OR SECONDARYINS = 'Spectera' OR  SECONDARYINS = 'SUPERIOR VISION';

/*REALIGN ALL INSURANCE SO THAT SECONDARY IS MOVED TO PRIMARY IF PRIMARY IS NULL*/
UPDATE MEANINGFULEXAMPLE
SET PRIMARYINS = SECONDARYINS, SECONDARYINS = NULL
WHERE (SECONDARYINS IS NOT NULL) AND (PRIMARYINS IS NULL);

/*REALIGN ALL CPT CODES SO THAT THE NULL CPT BOXES ARE ALWAYS LAST*/
SET SERVEROUTPUT ON
DECLARE 
  i NUMBER := 1;
  anum NUMBER := 1;
  n NUMBER := 0;
  temp VARCHAR2(26) := NULL;
  TYPE cpt_array  IS VARRAY(4) OF VARCHAR2(26);
  cpts            cpt_array := cpt_array(NULL,NULL,NULL,NULL);
BEGIN
  SELECT COUNT(*) 
  INTO n
  FROM MEANINGFULEXAMPLE;
  LOOP
  /*restart tracking number for array and array values every new row*/
  anum := 1;
  cpts(1) := NULL;
  cpts(2) := NULL;
  cpts(3) := NULL;
  cpts(4) := NULL;
  SELECT CPT1 
  INTO temp
  FROM MEANINGFULEXAMPLE
  WHERE ROWNUMBER = i;
  IF temp IS NOT NULL THEN 
    cpts(anum) := temp;
    anum := anum+1;
  END IF;
  SELECT CPT2 
  INTO temp
  FROM MEANINGFULEXAMPLE
  WHERE ROWNUMBER = i;
  IF temp IS NOT NULL THEN 
    cpts(anum) := temp;
    anum := anum+1;
  END IF;
  SELECT CPT3 
  INTO temp
  FROM MEANINGFULEXAMPLE
  WHERE ROWNUMBER = i;
  IF temp IS NOT NULL THEN 
    cpts(anum) := temp;
    anum := anum+1;
  END IF;
  SELECT CPT4 
  INTO temp
  FROM MEANINGFULEXAMPLE
  WHERE ROWNUMBER = i;
  IF temp IS NOT NULL THEN 
    cpts(anum) := temp;
    DBMS_OUTPUT.PUT_LINE(cpts(anum));
  END IF;
  DBMS_OUTPUT.PUT_LINE(i);
  UPDATE MEANINGFULEXAMPLE
  SET CPT1 = cpts(1), CPT2 = cpts(2), CPT3 = cpts(3), CPT4 = cpts(4)
  WHERE ROWNUMBER = i;
  i := i+1;
  IF i > n THEN
    EXIT;
  END IF;
  END LOOP;
END;
/

/*DELETE ALL ROWS THAT HAVE NO CPT CODES OR NO INSURANCE*/
DELETE FROM MEANINGFULEXAMPLE WHERE CPT1 IS NULL OR PRIMARYINS IS NULL;

And finally, in this dummy data, we're only left with one row that fits our criteria. With the real office data, we have sifted through a massive year's worth of data in seconds and have thousands of rows all presenting the data needed properly to immediately send off to Medicare.

Transferring Manual Data Collection to SQL Database

Below can be seen the process used for my clinic - dummy data is used here.