Elusive Data: Addresses and Phone Numbers

First of all, I must apologize again as I have not updated this BLOG in quite awhile. As it turns out, I'm not very good at regular writing....good to know!:) Anyway, on to today's topic.

Much of Banner's data is stored in a manner that can make it difficult to find the most up-to-date information. Good examples of this situation are addresses and phone numbers. It can be very difficult to find an individual's most current (let alone correct) address and phone number, especially when substandard data standards are used for the entry of that information. So, firstly, one must put the proper procedures in place to control accurate entry of contact information. To aid in this effort, we can provide several "audit queries" that will help identify common business problems in our address and phone number records.

Audit Your Data!

In my experience, address and phone errors fall into several different areas:

1. Addresses that have a "To" date in the past, but are still marked as active.
2. More than one active address of the same address type.
3. The "To" date is NULL, but the address is inactive.
4. The "From" date is NULL.
5. More than one active telephone number for the same telephone type.
6. Having an inactive phone number marked as primary.
7. More than one primary telephone number per individual.

I'm sure many institutions will have other common "opportunities" when it comes to their contact information, but in any case, after those issues are identified, it simply becomes a matter of querying the database on a regular basis (be it weekly, monthly, etc) to locate those records that are "out of compliance". The following queries will aid in finding the issues mentioned above:


--past to dates for active addresses
select spraddr_pidm,
       count(*) address_count
  from spraddr
 where (spraddr_status_ind is null or spraddr_status_ind = 'A')
   and spraddr_to_date < sysdate
 group by spraddr_pidm;


--more than one active address
select spraddr_pidm,
       spraddr_atyp_code,
       count(*) address_count
  from spraddr
 where spraddr_status_ind is null or spraddr_status_ind = 'A'
 group by spraddr_pidm, spraddr_atyp_code
 having count(*) > 1;


select spraddr_pidm,
       count(*) address_count
  from spraddr
 where spraddr_to_date is null
   and spraddr_status_ind <> 'A'
   and spraddr_status_ind is not null
 group by spraddr_pidm;


--null from date
select spraddr_pidm,
       count(*) address_count
  from spraddr
 where spraddr_from_date is null
 group by spraddr_pidm;


--multiple active phones per phone type
select sprtele_pidm,
       sprtele_tele_code,
       count(*) tele_count
  from sprtele
 where sprtele_status_ind is null
    or sprtele_status_ind = 'A'
 group by sprtele_pidm, sprtele_tele_code
 having count(*) > 1;


--individuals with inactive primary tele numbers
select sprtele_pidm,
       count(*) tele_count
  from sprtele
 where sprtele_status_ind is not null
   and sprtele_status_ind <> 'A'
   and sprtele_primary_ind = 'Y'
 group by sprtele_pidm;


select sprtele_pidm,
       count(*) tele_count
  from sprtele
 where sprtele_primary_ind = 'Y'
 group by sprtele_pidm
 having count(*) > 1;


As you can see, the queries are all very simple, but provide powerful results. With the list of pidms generated by each query, one could build an automated audit process for addresses and phone numbers. This would obviously need to be modified to include business rules at your institution, but the concept is the same. Additionally, there is an excellent product out there called Clean Address (by Runner Technologies) that is specifically designed to comb your addresses and verify their accuracy. Its moderately priced, but well worth it when you consider how much money is wasted on postage paid for correspondance that never reaches the intended recipient.

In any case, now that we've made a concerted effort to clean our data and put business processes in place to ensure as consistant and accurate input as possible, it's time to figure out how to query the contact information for an individual and get their most up-to-date information.

The info is IN, now how do we get it OUT?!

This is a difficult topic to cover in a broad sense because the approach you take depends completely on your goals. For example, if you're simply looking for their most current mailing (typically MA = mailing) address, it's a relatively simple process (assuming you've cleaned things up as described above). At that point, the query simply looks for the active address for each entity with the atyp code equal to MA (see below)!


select spraddr_pidm,
       spraddr_street_line1,
       spraddr_street_line2,
       spraddr_city,
       spraddr_stat_code,
       spraddr_zip
  from spraddr
 where spraddr_atyp_code = 'MA'
   and (spraddr_status_ind is null
     or spraddr_status_ind = 'A');


Assuming you've checked to make sure each individual only has ONE active address per address type, this query will work like a charm! You only have to get creative (using from/to dates and sequence numbers) when this isn't the case! NOTE: the same idea holds for telephone numbers.

Unfortunately, we typically want to bring back an address based on a hierarchy. This can be a bit tricky to make happen, but here are a few ideas for a solution! Firstly, Banner provides a form called GTVSDAX in which you can setup institutional hierarchies for things like addresses and telephone numbers. Once that's done, querying the database using a hierarchy becomes trivial; check it out!


select spraddr_pidm,
       spraddr_street_line1,
       spraddr_street_line2,
       spraddr_city,
       spraddr_stat_code,
       spraddr_zip
  from spraddr
 where (spraddr_status_ind is null
     or spraddr_status_ind = 'A')
   and spraddr.rowid = f_get_address_rowid(spraddr_pidm,'ADMDDR','A',SYSDATE,1,'S','');


As you can see, it's simply a matter of using the predifined Banner function and hierarchy to find the correct address for each individual. Alas, some schools don't use or know about the GTVSDAX form and as such require more "technical" answers to this dilemma. I happened to work for one such school myself! While there, I developed an Oracle function that would allow me to get the correct address type for each person based on a hierarchy. Bear in mind that I wrote this a long while ago, so it's not my best work, but it should point you in the right direction!


create or replace FUNCTION F_GET_ADDR_INFO (V_PIDM NUMBER, V_ATYP1 VARCHAR2,
V_ATYP2 VARCHAR2, V_ATYP3 VARCHAR2, V_ATYP4 VARCHAR2, WHICH VARCHAR2) RETURN VARCHAR2 AS

 V_OUTPUT VARCHAR2(500);

BEGIN
  SELECT DECODE(WHICH,'ADDRESS',SPRADDR_STREET_LINE1 || ' ' || SPRADDR_STREET_LINE2 || ' ' || SPRADDR_STREET_LINE3,
                      'CSZ', SPRADDR_CITY || ' ' || SPRADDR_STAT_CODE || ', ' || SPRADDR_ZIP,
                      'ATYP',SPRADDR_ATYP_CODE) INTO V_OUTPUT
    FROM SPRADDR
   WHERE SPRADDR_PIDM = V_PIDM
     AND (SPRADDR_ATYP_CODE = V_ATYP1 OR
          SPRADDR_ATYP_CODE = V_ATYP2 OR
          SPRADDR_ATYP_CODE = V_ATYP4 OR
          SPRADDR_ATYP_CODE = V_ATYP3)
     AND ROWNUM = 1;
  RETURN V_OUTPUT;
END F_GET_ADDR_INFO;


For telephone numbers, we're typically looking for a specific telephone type rather than one type in a hierarchy. That being the case, you can use a "pivot" query to produce the desired results (see my article regarding pivot queries) from last month!

Well, that's all for now. I appreciate your stopping by! Remember, as the old adage goes, "garbage in, garbage out", so always start your data search with an evaluation of your current data and business processes (when possible). This may be painful in the short term, but before long you'll be reaping vast rewards! Happy hunting!!!