This query returns information about how many different unique numbers are associated with a particular entity, by Entity ID. You might find this query useful if each entity typically only has one unique number. Checking to see if entities contain many different types of unique numbers is an excellent way to check for data anomalies and verify that your resolution rules are working as expected.
select distinct * from (select entity_id, (select count(distinct num_value) from nums, num_type Where nums.num_type_id=num.type.num_type_id and num_type.unique_FLAG='Y' and nums.entity_id=dsrc_acct.entity_id ) as UNIQUE_NUMBER_CNT from dscr_acct )as tab1 where UNIQUE_NUMBER_CNT>1 order by UNIQUE_NUMBER_CNT DESC;
In the Visualizer, use the Find by Entity ID screen to look up the Entity IDs returned from the Total Number of Unique Numbers by Entity ID query results. By reviewing the entity resume for each entity, you can determine if the entity should have more than one unique number. In some cases, this situation might be an indication of fraud. For example, in the United States Social Security Numbers (SSNs) are unique numbers. Typically, each U.S. entity only has one SSN. If this query uncovers an entity that has multiple SSNs, the next step is probably to do further investigation and analysis of why the entity has multiple SSNs.
Here is an example of what the results of running the Total Number of Unique Number by Entity query might look like:
ENTITY_ID | UNIQUE_NUMBER_CNT |
---|---|
3003 | 2 |
3030 | 2 |
3039 | 2 |