--Attribute Density

set linesize 500
set pagesize 500
set echo on
set trimspool off
SET FEEDBACK off
SET TIMING on
set heading off
set sqlblanklines on

--drop table T_PCR_Attribute_Density;

Create Table T_PCR_Attribute_Density as
select ALL_SOURCES.srccode All_Sources_SrcCode, ALL_SOURCES.srcname All_Sources_SrcName, ALL_SOURCES.total All_Sources_Total, MRN.total MRN_Total, round((MRN.total/ALL_SOURCES.total)*100,2) Percent_MRN_Populated, HCN.total HCN_Total, round((HCN.total/ALL_SOURCES.total)*100,2) Percent_HCN_Populated, LEGAL_LAST_NAME.total Legal_Last_Name_Total, round((LEGAL_LAST_NAME.total/ALL_SOURCES.total)*100,2) Percent_Legal_Last_Name_Populated, LEGAL_MIDDLE_NAME.total Legal_Middle_Name_Total, round((LEGAL_MIDDLE_NAME.total/ALL_SOURCES.total)*100,2) Percent_Legal_Middle_Name_Populated, LEGAL_FIRST_NAME.total Legal_First_Name_Total, round((LEGAL_FIRST_NAME.total/ALL_SOURCES.total)*100,2) Percent_Legal_First_Name_Populated, LEGAL_NAME_PREFIX.total Legal_Name_Prefix_Total, round((LEGAL_NAME_PREFIX.total/ALL_SOURCES.total)*100,2) Percent_Legal_Name_Prefix_Populated, ALIAS1_LAST_NAME.total Alias1_Last_Name_Total, round((ALIAS1_LAST_NAME.total/ALL_SOURCES.total)*100,2) Percent_Alias1_Last_Name_Populated, ALIAS1_MIDDLE_NAME.total Alias1_Middle_Name_Total, round((ALIAS1_MIDDLE_NAME.total/ALL_SOURCES.total)*100,2) Percent_Alias1_Middle_Name_Populated, ALIAS1_FIRST_NAME.total Alias1_First_Name_Total, round((ALIAS1_FIRST_NAME.total/ALL_SOURCES.total)*100,2) Percent_Alias1_First_Name_Populated, ALIAS1_NAME_PREFIX.total Alias1_Name_Prefix_Total, round((ALIAS1_NAME_PREFIX.total/ALL_SOURCES.total)*100,2) Percent_Alias1_Name_Prefix_Populated, NOK_LAST_NAME.total Nok_Last_Name_Total, round((NOK_LAST_NAME.total/ALL_SOURCES.total)*100,2) Percent_Nok_Last_Name_Populated, NOK_MIDDLE_NAME.total Nok_Middle_Name_Total, round((NOK_MIDDLE_NAME.total/ALL_SOURCES.total)*100,2) Percent_Nok_Middle_Name_Populated, NOK_FIRST_NAME.total Nok_First_Name_Total, round((NOK_FIRST_NAME.total/ALL_SOURCES.total)*100,2) Percent_Nok_First_Name_Populated, NOK_NAME_PREFIX.total Nok_Name_Prefix_Total, round((NOK_NAME_PREFIX.total/ALL_SOURCES.total)*100,2) Percent_Nok_Name_Prefix_Populated, HOME_PRIMARY_ADDRESS_LINE1.total Home_Primary_Address_Line1_Total, round((HOME_PRIMARY_ADDRESS_LINE1.total/ALL_SOURCES.total)*100,2) Percent_Home_Primary_Address_Line1_Populated, HOME_PRIMARY_ADDRESS_LINE2.total Home_Primary_Address_Line2_Total, round((HOME_PRIMARY_ADDRESS_LINE2.total/ALL_SOURCES.total)*100,2) Percent_Home_Primary_Address_Line2_Populated, HOME_PRIMARY_ADDRESS_CITY.total Home_Primary_Address_City_Total, round((HOME_PRIMARY_ADDRESS_CITY.total/ALL_SOURCES.total)*100,2) Percent_Home_Primary_Address_City_Populated, HOME_PRIMARY_ADDRESS_STATE.total Home_Primary_Address_State_Total, round((HOME_PRIMARY_ADDRESS_STATE.total/ALL_SOURCES.total)*100,2) Percent_Home_Primary_Address_State_Populated, HOME_PRIMARY_ADDRESS_COUNTRY.total Home_Primary_Address_Country_Total, round((HOME_PRIMARY_ADDRESS_COUNTRY.total/ALL_SOURCES.total)*100,2) Percent_Home_Primary_Address_Country_Populated, HOME_PRIMARY_ADDRESS_ZIPCODE.total Home_Primary_Address_ZipCode_Total, round((HOME_PRIMARY_ADDRESS_ZIPCODE.total/ALL_SOURCES.total)*100,2) Percent_Home_Primary_Address_ZipCode_Populated, MAILING_ADDRESS_LINE1.total Mailing_Address_Line1_Total, round((MAILING_ADDRESS_LINE1.total/ALL_SOURCES.total)*100,2) Percent_Mailing_Address_Line1_Populated, MAILING_ADDRESS_LINE2.total Mailing_Address_Line2_Total, round((MAILING_ADDRESS_LINE2.total/ALL_SOURCES.total)*100,2) Percent_Mailing_Address_Line2_Populated, MAILING_ADDRESS_CITY.total Mailing_Address_City_Total, round((MAILING_ADDRESS_CITY.total/ALL_SOURCES.total)*100,2) Percent_Mailing_Address_City_Populated, MAILING_ADDRESS_STATE.total Mailing_Address_State_Total, round((MAILING_ADDRESS_STATE.total/ALL_SOURCES.total)*100,2) Percent_Mailing_Address_State_Populated, MAILING_ADDRESS_COUNTRY.total Mailing_Address_Country_Total, round((MAILING_ADDRESS_COUNTRY.total/ALL_SOURCES.total)*100,2) Percent_Mailing_Address_Country_Populated, MAILING_ADDRESS_ZIPCODE.total Mailing_Address_ZipCode_Total, round((MAILING_ADDRESS_ZIPCODE.total/ALL_SOURCES.total)*100,2) Percent_Mailing_Address_ZipCode_Populated, TEMP_ADDRESS_LINE1.total Temp_Address_Line1_Total, round((TEMP_ADDRESS_LINE1.total/ALL_SOURCES.total)*100,2) Percent_Temp_Address_Line1_Populated, TEMP_ADDRESS_LINE2.total Temp_Address_Line2_Total, round((TEMP_ADDRESS_LINE2.total/ALL_SOURCES.total)*100,2) Percent_Temp_Address_Line2_Populated, TEMP_ADDRESS_CITY.total Temp_Address_City_Total, round((TEMP_ADDRESS_CITY.total/ALL_SOURCES.total)*100,2) Percent_Temp_Address_City_Populated, TEMP_ADDRESS_STATE.total Temp_Address_State_Total, round((TEMP_ADDRESS_STATE.total/ALL_SOURCES.total)*100,2) Percent_Temp_Address_State_Populated, TEMP_ADDRESS_COUNTRY.total Temp_Address_Country_Total, round((TEMP_ADDRESS_COUNTRY.total/ALL_SOURCES.total)*100,2) Percent_Temp_Address_Country_Populated, TEMP_ADDRESS_ZIPCODE.total Temp_Address_ZipCode_Total,
 round((TEMP_ADDRESS_ZIPCODE.total/ALL_SOURCES.total)*100,2) Percent_Temp_Address_ZipCode_Populated, PRIM_RES_PHONE_AREA.total Primary_Residence_Phone_AreaCode_Total, round((PRIM_RES_PHONE_AREA.total/ALL_SOURCES.total)*100,2) Percent_Primary_Residence_Phone_AreaCode_Populated, PRIM_RES_PHONE_NUMBER.total Primary_Residence_Phone_Number_Total, round((PRIM_RES_PHONE_NUMBER.total/ALL_SOURCES.total)*100,2) Percent_Primary_Residence_Phone_Number_Populated, EMERGENCY_PHONE_AREA.total Emergency_Phone_AreaCode_Total, round((EMERGENCY_PHONE_AREA.total/ALL_SOURCES.total)*100,2) Percent_Emergency_Phone_AreaCode_Populated, EMERGENCY_PHONE_NUMBER.total Emergency_Phone_Number_Total, round((EMERGENCY_PHONE_NUMBER.total/ALL_SOURCES.total)*100,2) Percent_Emergency_Phone_Number_Populated, OTHER_PHONE_AREA.total Other_Phone_AreaCode_Total, round((OTHER_PHONE_AREA.total/ALL_SOURCES.total)*100,2) Percent_Other_Phone_AreaCode_Populated, OTHER_PHONE_NUMBER.total Other_Phone_Number_Total, round((OTHER_PHONE_NUMBER.total/ALL_SOURCES.total)*100,2) Percent_Other_Phone_Number_Populated, BUSINESS_PHONE_AREA.total Business_Phone_AreaCode_Total, round((BUSINESS_PHONE_AREA.total/ALL_SOURCES.total)*100,2) Percent_Business_Phone_AreaCode_Populated, BUSINESS_PHONE_NUMBER.total Business_Phone_Number_Total, round((BUSINESS_PHONE_NUMBER.total/ALL_SOURCES.total)*100,2) Percent_Business_Phone_Number_Populated, BUSINESS_PHONE_EXTENSION.total Busienss_Phone_Extension_Total, round((BUSINESS_PHONE_EXTENSION.total/ALL_SOURCES.total)*100,2) Percent_Busienss_Phone_Extension_Populated, BIRTH_DATE.total Birth_Date_Total, round((BIRTH_DATE.total/ALL_SOURCES.total)*100,2) Percent_Birth_Date_Populated, DEATH_EVENT_DATE.total Death_Event_Date_Total, round((DEATH_EVENT_DATE.total/ALL_SOURCES.total)*100,2) Percent_Death_Event_Date_Populated, RECORD_CREATION_DATE.total Record_Creation_Date_Total, round((RECORD_CREATION_DATE.total/ALL_SOURCES.total)*100,2) Percent_Record_Creation_Date_Populated, CORP_ID.total Corp_ID_Total, round((CORP_ID.total/ALL_SOURCES.total)*100,2) Percent_Corp_ID_Populated, GENDER.total Gender_Total, round((GENDER.total/ALL_SOURCES.total)*100,2) Percent_Gender_Populated, CONFIDENTIALLY.total Confidentially_Total, round((CONFIDENTIALLY.total/ALL_SOURCES.total)*100,2) Percent_Confidentially_Populated, DEATH_INDICATOR.total Death_Indicator_Total, round((DEATH_INDICATOR.total/ALL_SOURCES.total)*100,2) Percent_Death_Indicator_Populated
from
--ALL DEFINITIONAL SOURCES
(select mpi_srchead.srccode,mpi_srchead.srcname, count(mpi_memhead.memrecno) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where mpi_memhead.srcrecno in (724,672)
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode,mpi_srchead.srcname) as ALL_SOURCES
left outer join
--MRN COUNT
(select mpi_srchead.srccode, count(mpi_memident.idnumber) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join mpi_memident on mpi_memident.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where mpi_memident.recstat = 'A'
and mpi_memhead.srcrecno in (724, 672)
and mpi_memident.attrrecno = 132 --MRN
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as MRN on ALL_SOURCES.srccode = MRN.srccode
left outer join
--HCN COUNT
(select mpi_srchead.srccode,count(mpi_memident.idnumber) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join mpi_memident on mpi_memident.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where mpi_memident.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and mpi_memident.attrrecno = 104 --HCN
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as HCN on ALL_SOURCES.srccode = HCN.srccode
left outer join
--LEGAL LAST NAME COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmlast) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 101
and MPI_PCR_MEMNAME.onmlast NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as LEGAL_LAST_NAME on ALL_SOURCES.srccode = LEGAL_LAST_NAME.srccode
left outer join
--LEGAL MIDDLE NAME COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmmiddle) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 101
and MPI_PCR_MEMNAME.onmmiddle NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as LEGAL_MIDDLE_NAME on ALL_SOURCES.srccode = LEGAL_MIDDLE_NAME.srccode
left outer join
--LEGAL FIRST NAME COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmfirst) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 101
and MPI_PCR_MEMNAME.onmfirst NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as LEGAL_FIRST_NAME on ALL_SOURCES.srccode = LEGAL_FIRST_NAME.srccode
left outer join
--LEGAL NAME PREFIX COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmprefix) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 101
and MPI_PCR_MEMNAME.onmprefix NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'LASTPFX')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as LEGAL_NAME_PREFIX on ALL_SOURCES.srccode = LEGAL_NAME_PREFIX.srccode
left outer join
--ALIAS 1 LAST NAME COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmlast) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 103
and MPI_PCR_MEMNAME.onmlast NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as ALIAS1_LAST_NAME on ALL_SOURCES.srccode = ALIAS1_LAST_NAME.srccode
left outer join
--ALIAS 1 MIDDLE NAME COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmmiddle) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 103
and MPI_PCR_MEMNAME.onmmiddle NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as ALIAS1_MIDDLE_NAME on ALL_SOURCES.srccode = ALIAS1_MIDDLE_NAME.srccode
left outer join
--ALIAS 1 FIRST NAME COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmfirst) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 103
and MPI_PCR_MEMNAME.onmfirst NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as ALIAS1_FIRST_NAME on ALL_SOURCES.srccode = ALIAS1_FIRST_NAME.srccode
left outer join
--ALIAS 1 NAME PREFIX COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmprefix) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 103
and MPI_PCR_MEMNAME.onmprefix NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'LASTPFX')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as ALIAS1_NAME_PREFIX on ALL_SOURCES.srccode = ALIAS1_NAME_PREFIX.srccode
left outer join
--NEXT OF KIN LAST NAME COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmlast) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 126
and MPI_PCR_MEMNAME.onmlast NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as NOK_LAST_NAME on ALL_SOURCES.srccode = NOK_LAST_NAME.srccode
left outer join
--NEXT OF KIN MIDDLE NAME COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmmiddle) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 126
and MPI_PCR_MEMNAME.onmmiddle NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as NOK_MIDDLE_NAME on ALL_SOURCES.srccode = NOK_MIDDLE_NAME.srccode
left outer join
--NEXT OF KIN FIRST NAME COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmfirst) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 126
and MPI_PCR_MEMNAME.onmfirst NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as NOK_FIRST_NAME on ALL_SOURCES.srccode = NOK_FIRST_NAME.srccode
left outer join
--NEXT OF KIN NAME PREFIX COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMNAME.onmprefix) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMNAME on MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMNAME.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMNAME.attrrecno = 126
and MPI_PCR_MEMNAME.onmprefix NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'LASTPFX')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as NOK_NAME_PREFIX on ALL_SOURCES.srccode = NOK_NAME_PREFIX.srccode
left outer join
--Home Primary Address Line 1 COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.stline1) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 107
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as HOME_PRIMARY_ADDRESS_LINE1 on ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_LINE1.srccode
left outer join
--Home Primary Address Line 2 COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.stline2) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 107
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as HOME_PRIMARY_ADDRESS_LINE2 on ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_LINE2.srccode
left outer join
--Home Primary Address City COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.city) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 107
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as HOME_PRIMARY_ADDRESS_CITY on ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_CITY.srccode
left outer join
--Home Primary Address State COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.state) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 107
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as HOME_PRIMARY_ADDRESS_STATE on ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_STATE.srccode
left outer join
--Home Primary Address Country COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.country) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 107
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as HOME_PRIMARY_ADDRESS_COUNTRY on ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_COUNTRY.srccode
left outer join
--Home Primary Address Zipcode COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.zipcode) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 107
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as HOME_PRIMARY_ADDRESS_ZIPCODE on ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_ZIPCODE.srccode
left outer join
--Mailing Address Line 1 COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.stline1) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 135
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as MAILING_ADDRESS_LINE1 on ALL_SOURCES.srccode = MAILING_ADDRESS_LINE1.srccode
left outer join
--Mailing Address Line 2 COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.stline2) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 135
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as MAILING_ADDRESS_LINE2 on ALL_SOURCES.srccode = MAILING_ADDRESS_LINE2.srccode
left outer join
--Mailing Address City COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.city) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 135
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as MAILING_ADDRESS_CITY on ALL_SOURCES.srccode = MAILING_ADDRESS_CITY.srccode
left outer join
--Mailing Address State COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.state) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 135
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as MAILING_ADDRESS_STATE on ALL_SOURCES.srccode = MAILING_ADDRESS_STATE.srccode
left outer join
--Mailing Address Country COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.country) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 135
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as MAILING_ADDRESS_COUNTRY on ALL_SOURCES.srccode = MAILING_ADDRESS_COUNTRY.srccode
left outer join
--Mailing Address Zipcode COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.zipcode) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 135
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as MAILING_ADDRESS_ZIPCODE on ALL_SOURCES.srccode = MAILING_ADDRESS_ZIPCODE.srccode
left outer join
--Temp Address Line 1 COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.stline1) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 136
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as TEMP_ADDRESS_LINE1 on ALL_SOURCES.srccode = TEMP_ADDRESS_LINE1.srccode
left outer join
--Temp Address Line 2 COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.stline2) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 136
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as TEMP_ADDRESS_LINE2 on ALL_SOURCES.srccode = TEMP_ADDRESS_LINE2.srccode
left outer join
--Temp Address City COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.city) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 136
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as TEMP_ADDRESS_CITY on ALL_SOURCES.srccode = TEMP_ADDRESS_CITY.srccode
left outer join
--Temp Address State COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.state) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 136
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as TEMP_ADDRESS_STATE on ALL_SOURCES.srccode = TEMP_ADDRESS_STATE.srccode
left outer join
--Temp Address Country COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.country) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 136
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as TEMP_ADDRESS_COUNTRY on ALL_SOURCES.srccode = TEMP_ADDRESS_COUNTRY.srccode
left outer join
--Temp Address Zipcode COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMADDR.zipcode) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMADDR on MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMADDR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMADDR.attrrecno = 136
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as TEMP_ADDRESS_ZIPCODE on ALL_SOURCES.srccode = TEMP_ADDRESS_ZIPCODE.srccode
left outer join
--Primary Res Phone AreaCode COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMPHONE.pharea) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMPHONE on MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMPHONE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMPHONE.attrrecno = 108
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as PRIM_RES_PHONE_AREA on ALL_SOURCES.srccode = PRIM_RES_PHONE_AREA.srccode
left outer join
--Primary Res Phone Number COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMPHONE.phnumber) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMPHONE on MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMPHONE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMPHONE.attrrecno = 108
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as PRIM_RES_PHONE_NUMBER on ALL_SOURCES.srccode = PRIM_RES_PHONE_NUMBER.srccode
left outer join
--Emergency Phone AreaCode COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMPHONE.pharea) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMPHONE on MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMPHONE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMPHONE.attrrecno = 138
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as EMERGENCY_PHONE_AREA on ALL_SOURCES.srccode = EMERGENCY_PHONE_AREA.srccode
left outer join
--Emergency Phone Number COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMPHONE.phnumber) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMPHONE on MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMPHONE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMPHONE.attrrecno = 138
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as EMERGENCY_PHONE_NUMBER on ALL_SOURCES.srccode = EMERGENCY_PHONE_NUMBER.srccode
left outer join
--Other Phone AreaCode COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMPHONE.pharea) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMPHONE on MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMPHONE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMPHONE.attrrecno = 109
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as OTHER_PHONE_AREA on ALL_SOURCES.srccode = OTHER_PHONE_AREA.srccode
left outer join
--Other Phone Number COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMPHONE.phnumber) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMPHONE on MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMPHONE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMPHONE.attrrecno = 109
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as OTHER_PHONE_NUMBER on ALL_SOURCES.srccode = OTHER_PHONE_NUMBER.srccode
left outer join
--Business Phone AreaCode COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMPHONE.pharea) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMPHONE on MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMPHONE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMPHONE.attrrecno = 137
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as BUSINESS_PHONE_AREA on ALL_SOURCES.srccode = BUSINESS_PHONE_AREA.srccode
left outer join
--Business Phone Number COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMPHONE.phnumber) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMPHONE on MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMPHONE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMPHONE.attrrecno = 137
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as BUSINESS_PHONE_NUMBER on ALL_SOURCES.srccode = BUSINESS_PHONE_NUMBER.srccode
left outer join
--Business Phone Extension COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMPHONE.phextn) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMPHONE on MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMPHONE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMPHONE.attrrecno = 137
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as BUSINESS_PHONE_EXTENSION on ALL_SOURCES.srccode = BUSINESS_PHONE_EXTENSION.srccode
left outer join
--Birth date COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMDATE.memrecno) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMDATE on MPI_PCR_MEMDATE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMDATE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMDATE.attrrecno = 122
AND MPI_PCR_MEMDATE.dateval NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'DATE')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as BIRTH_DATE on ALL_SOURCES.srccode = BIRTH_DATE.srccode
left outer join
--Death Event date COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMDATE.memrecno) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMDATE on MPI_PCR_MEMDATE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMDATE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMDATE.attrrecno = 128
AND MPI_PCR_MEMDATE.dateval NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'DATE')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as DEATH_EVENT_DATE on ALL_SOURCES.srccode = DEATH_EVENT_DATE.srccode
left outer join
--Record Creation date COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMDATE.memrecno) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMDATE on MPI_PCR_MEMDATE.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMDATE.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMDATE.attrrecno = 124
AND MPI_PCR_MEMDATE.dateval NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'DATE')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as RECORD_CREATION_DATE on ALL_SOURCES.srccode = RECORD_CREATION_DATE.srccode
left outer join
--CorpID COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMATTR.memrecno) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMATTR on MPI_PCR_MEMATTR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMATTR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMATTR.attrrecno = 133
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as CORP_ID on ALL_SOURCES.srccode = CORP_ID.srccode
left outer join
--Gender COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMATTR.memrecno) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMATTR on MPI_PCR_MEMATTR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMATTR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMATTR.attrrecno = 123
AND MPI_PCR_MEMATTR.attrval in ('M','F')
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as GENDER on ALL_SOURCES.srccode = GENDER.srccode
left outer join
--Confidentially COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMATTR.memrecno) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMATTR on MPI_PCR_MEMATTR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMATTR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMATTR.attrrecno = 129
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as CONFIDENTIALLY on ALL_SOURCES.srccode = CONFIDENTIALLY.srccode
left outer join
--Death Indicator COUNT
(select mpi_srchead.srccode,count(MPI_PCR_MEMATTR.memrecno) as total
from mpi_memhead
inner join mpi_srchead on mpi_srchead.srcrecno = mpi_memhead.srcrecno
inner join MPI_PCR_MEMATTR on MPI_PCR_MEMATTR.memrecno = mpi_memhead.memrecno
inner join mpi_audhead on mpi_memhead.caudrecno = mpi_audhead.audrecno
where MPI_PCR_MEMATTR.recstat = 'A'
and mpi_memhead.srcrecno in (724,672)
and MPI_PCR_MEMATTR.attrrecno = 127
--and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
group by mpi_srchead.srccode) as DEATH_INDICATOR on ALL_SOURCES.srccode = DEATH_INDICATOR.srccode;