Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 18 Aug 2003 @ 20:51:13 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Assigning destination tags to CDRs
 
From:   Jeff Ohlman

I don't know how phone numbers are comprised in Germany, but in the United States we divide the phone-number into three components: area- code, prefix and line. I'll call these three AC CHAR(3), PR CHAR(3) and LN CHAR(4).

Simply breaking up the number into AC, PR and LN as the NUPI. Then create a NUSI on AC and PR. Collect stats on both NUPI and NUSI. If the level of detail records causes too many hash-collisions then you would need to add additional columns to the NUPI. The key , though, is to NUSI the AC and PR to prevent full-table scans.

Create a look-up table for AC and PR that decodes to state, central-office, etc. This table is UPI on AC and PR.

This should allow you to do very efficient joins on your call detail. In my experience this methodology works very well.

I assume you are mloading the call detail so spliting the phone number should be as simple as spliting the file layout in the appropriate position.

This assumes that the phone number is always left padded with zeros. i.e: 404-377-0001 would never appear as 404-377-1. If it did then you would need to do a recasting of the data prior to loading. This could be done with perl or with SQL depending on your preference.



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023