Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement