API tools faq
paste
Advertisement
clarenceb5

Attribute Density

clarenceb5
Apr 3rd, 2024 (edited)
429
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 39.15 KB | Source Code | 0 0
raw download clone embed print report
  1. --Attribute Density
  2.  
  3. SET linesize 500
  4. SET pagesize 500
  5. SET echo ON
  6. SET trimspool off
  7. SET FEEDBACK off
  8. SET TIMING ON
  9. SET heading off
  10. SET sqlblanklines ON
  11.  
  12. --drop table T_PCR_Attribute_Density;
  13.  
  14. CREATE TABLE T_PCR_Attribute_Density AS
  15. 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,
  16.  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
  17. FROM
  18. --ALL DEFINITIONAL SOURCES
  19. (SELECT mpi_srchead.srccode,mpi_srchead.srcname, COUNT(mpi_memhead.memrecno) AS total
  20. FROM mpi_memhead
  21. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  22. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  23. WHERE mpi_memhead.srcrecno IN (724,672)
  24. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  25. GROUP BY mpi_srchead.srccode,mpi_srchead.srcname) AS ALL_SOURCES
  26. LEFT OUTER JOIN
  27. --MRN COUNT
  28. (SELECT mpi_srchead.srccode, COUNT(mpi_memident.idnumber) AS total
  29. FROM mpi_memhead
  30. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  31. INNER JOIN mpi_memident ON mpi_memident.memrecno = mpi_memhead.memrecno
  32. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  33. WHERE mpi_memident.recstat = 'A'
  34. AND mpi_memhead.srcrecno IN (724, 672)
  35. AND mpi_memident.attrrecno = 132 --MRN
  36. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  37. GROUP BY mpi_srchead.srccode) AS MRN ON ALL_SOURCES.srccode = MRN.srccode
  38. LEFT OUTER JOIN
  39. --HCN COUNT
  40. (SELECT mpi_srchead.srccode,COUNT(mpi_memident.idnumber) AS total
  41. FROM mpi_memhead
  42. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  43. INNER JOIN mpi_memident ON mpi_memident.memrecno = mpi_memhead.memrecno
  44. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  45. WHERE mpi_memident.recstat = 'A'
  46. AND mpi_memhead.srcrecno IN (724,672)
  47. AND mpi_memident.attrrecno = 104 --HCN
  48. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  49. GROUP BY mpi_srchead.srccode) AS HCN ON ALL_SOURCES.srccode = HCN.srccode
  50. LEFT OUTER JOIN
  51. --LEGAL LAST NAME COUNT
  52. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmlast) AS total
  53. FROM mpi_memhead
  54. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  55. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  56. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  57. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  58. AND mpi_memhead.srcrecno IN (724,672)
  59. AND MPI_PCR_MEMNAME.attrrecno = 101
  60. AND MPI_PCR_MEMNAME.onmlast NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
  61. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  62. GROUP BY mpi_srchead.srccode) AS LEGAL_LAST_NAME ON ALL_SOURCES.srccode = LEGAL_LAST_NAME.srccode
  63. LEFT OUTER JOIN
  64. --LEGAL MIDDLE NAME COUNT
  65. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmmiddle) AS total
  66. FROM mpi_memhead
  67. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  68. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  69. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  70. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  71. AND mpi_memhead.srcrecno IN (724,672)
  72. AND MPI_PCR_MEMNAME.attrrecno = 101
  73. AND MPI_PCR_MEMNAME.onmmiddle NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
  74. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  75. GROUP BY mpi_srchead.srccode) AS LEGAL_MIDDLE_NAME ON ALL_SOURCES.srccode = LEGAL_MIDDLE_NAME.srccode
  76. LEFT OUTER JOIN
  77. --LEGAL FIRST NAME COUNT
  78. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmfirst) AS total
  79. FROM mpi_memhead
  80. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  81. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  82. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  83. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  84. AND mpi_memhead.srcrecno IN (724,672)
  85. AND MPI_PCR_MEMNAME.attrrecno = 101
  86. AND MPI_PCR_MEMNAME.onmfirst NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
  87. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  88. GROUP BY mpi_srchead.srccode) AS LEGAL_FIRST_NAME ON ALL_SOURCES.srccode = LEGAL_FIRST_NAME.srccode
  89. LEFT OUTER JOIN
  90. --LEGAL NAME PREFIX COUNT
  91. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmprefix) AS total
  92. FROM mpi_memhead
  93. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  94. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  95. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  96. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  97. AND mpi_memhead.srcrecno IN (724,672)
  98. AND MPI_PCR_MEMNAME.attrrecno = 101
  99. AND MPI_PCR_MEMNAME.onmprefix NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'LASTPFX')
  100. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  101. GROUP BY mpi_srchead.srccode) AS LEGAL_NAME_PREFIX ON ALL_SOURCES.srccode = LEGAL_NAME_PREFIX.srccode
  102. LEFT OUTER JOIN
  103. --ALIAS 1 LAST NAME COUNT
  104. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmlast) AS total
  105. FROM mpi_memhead
  106. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  107. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  108. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  109. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  110. AND mpi_memhead.srcrecno IN (724,672)
  111. AND MPI_PCR_MEMNAME.attrrecno = 103
  112. AND MPI_PCR_MEMNAME.onmlast NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
  113. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  114. GROUP BY mpi_srchead.srccode) AS ALIAS1_LAST_NAME ON ALL_SOURCES.srccode = ALIAS1_LAST_NAME.srccode
  115. LEFT OUTER JOIN
  116. --ALIAS 1 MIDDLE NAME COUNT
  117. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmmiddle) AS total
  118. FROM mpi_memhead
  119. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  120. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  121. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  122. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  123. AND mpi_memhead.srcrecno IN (724,672)
  124. AND MPI_PCR_MEMNAME.attrrecno = 103
  125. AND MPI_PCR_MEMNAME.onmmiddle NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
  126. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  127. GROUP BY mpi_srchead.srccode) AS ALIAS1_MIDDLE_NAME ON ALL_SOURCES.srccode = ALIAS1_MIDDLE_NAME.srccode
  128. LEFT OUTER JOIN
  129. --ALIAS 1 FIRST NAME COUNT
  130. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmfirst) AS total
  131. FROM mpi_memhead
  132. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  133. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  134. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  135. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  136. AND mpi_memhead.srcrecno IN (724,672)
  137. AND MPI_PCR_MEMNAME.attrrecno = 103
  138. AND MPI_PCR_MEMNAME.onmfirst NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
  139. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  140. GROUP BY mpi_srchead.srccode) AS ALIAS1_FIRST_NAME ON ALL_SOURCES.srccode = ALIAS1_FIRST_NAME.srccode
  141. LEFT OUTER JOIN
  142. --ALIAS 1 NAME PREFIX COUNT
  143. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmprefix) AS total
  144. FROM mpi_memhead
  145. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  146. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  147. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  148. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  149. AND mpi_memhead.srcrecno IN (724,672)
  150. AND MPI_PCR_MEMNAME.attrrecno = 103
  151. AND MPI_PCR_MEMNAME.onmprefix NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'LASTPFX')
  152. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  153. GROUP BY mpi_srchead.srccode) AS ALIAS1_NAME_PREFIX ON ALL_SOURCES.srccode = ALIAS1_NAME_PREFIX.srccode
  154. LEFT OUTER JOIN
  155. --NEXT OF KIN LAST NAME COUNT
  156. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmlast) AS total
  157. FROM mpi_memhead
  158. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  159. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  160. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  161. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  162. AND mpi_memhead.srcrecno IN (724,672)
  163. AND MPI_PCR_MEMNAME.attrrecno = 126
  164. AND MPI_PCR_MEMNAME.onmlast NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
  165. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  166. GROUP BY mpi_srchead.srccode) AS NOK_LAST_NAME ON ALL_SOURCES.srccode = NOK_LAST_NAME.srccode
  167. LEFT OUTER JOIN
  168. --NEXT OF KIN MIDDLE NAME COUNT
  169. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmmiddle) AS total
  170. FROM mpi_memhead
  171. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  172. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  173. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  174. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  175. AND mpi_memhead.srcrecno IN (724,672)
  176. AND MPI_PCR_MEMNAME.attrrecno = 126
  177. AND MPI_PCR_MEMNAME.onmmiddle NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
  178. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  179. GROUP BY mpi_srchead.srccode) AS NOK_MIDDLE_NAME ON ALL_SOURCES.srccode = NOK_MIDDLE_NAME.srccode
  180. LEFT OUTER JOIN
  181. --NEXT OF KIN FIRST NAME COUNT
  182. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmfirst) AS total
  183. FROM mpi_memhead
  184. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  185. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  186. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  187. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  188. AND mpi_memhead.srcrecno IN (724,672)
  189. AND MPI_PCR_MEMNAME.attrrecno = 126
  190. AND MPI_PCR_MEMNAME.onmfirst NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'ANAME')
  191. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  192. GROUP BY mpi_srchead.srccode) AS NOK_FIRST_NAME ON ALL_SOURCES.srccode = NOK_FIRST_NAME.srccode
  193. LEFT OUTER JOIN
  194. --NEXT OF KIN NAME PREFIX COUNT
  195. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMNAME.onmprefix) AS total
  196. FROM mpi_memhead
  197. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  198. INNER JOIN MPI_PCR_MEMNAME ON MPI_PCR_MEMNAME.memrecno = mpi_memhead.memrecno
  199. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  200. WHERE MPI_PCR_MEMNAME.recstat = 'A'
  201. AND mpi_memhead.srcrecno IN (724,672)
  202. AND MPI_PCR_MEMNAME.attrrecno = 126
  203. AND MPI_PCR_MEMNAME.onmprefix NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'LASTPFX')
  204. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  205. GROUP BY mpi_srchead.srccode) AS NOK_NAME_PREFIX ON ALL_SOURCES.srccode = NOK_NAME_PREFIX.srccode
  206. LEFT OUTER JOIN
  207. --Home Primary Address Line 1 COUNT
  208. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.stline1) AS total
  209. FROM mpi_memhead
  210. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  211. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  212. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  213. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  214. AND mpi_memhead.srcrecno IN (724,672)
  215. AND MPI_PCR_MEMADDR.attrrecno = 107
  216. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  217. GROUP BY mpi_srchead.srccode) AS HOME_PRIMARY_ADDRESS_LINE1 ON ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_LINE1.srccode
  218. LEFT OUTER JOIN
  219. --Home Primary Address Line 2 COUNT
  220. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.stline2) AS total
  221. FROM mpi_memhead
  222. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  223. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  224. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  225. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  226. AND mpi_memhead.srcrecno IN (724,672)
  227. AND MPI_PCR_MEMADDR.attrrecno = 107
  228. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  229. GROUP BY mpi_srchead.srccode) AS HOME_PRIMARY_ADDRESS_LINE2 ON ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_LINE2.srccode
  230. LEFT OUTER JOIN
  231. --Home Primary Address City COUNT
  232. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.city) AS total
  233. FROM mpi_memhead
  234. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  235. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  236. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  237. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  238. AND mpi_memhead.srcrecno IN (724,672)
  239. AND MPI_PCR_MEMADDR.attrrecno = 107
  240. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  241. GROUP BY mpi_srchead.srccode) AS HOME_PRIMARY_ADDRESS_CITY ON ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_CITY.srccode
  242. LEFT OUTER JOIN
  243. --Home Primary Address State COUNT
  244. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.state) AS total
  245. FROM mpi_memhead
  246. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  247. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  248. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  249. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  250. AND mpi_memhead.srcrecno IN (724,672)
  251. AND MPI_PCR_MEMADDR.attrrecno = 107
  252. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  253. GROUP BY mpi_srchead.srccode) AS HOME_PRIMARY_ADDRESS_STATE ON ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_STATE.srccode
  254. LEFT OUTER JOIN
  255. --Home Primary Address Country COUNT
  256. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.country) AS total
  257. FROM mpi_memhead
  258. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  259. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  260. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  261. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  262. AND mpi_memhead.srcrecno IN (724,672)
  263. AND MPI_PCR_MEMADDR.attrrecno = 107
  264. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  265. GROUP BY mpi_srchead.srccode) AS HOME_PRIMARY_ADDRESS_COUNTRY ON ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_COUNTRY.srccode
  266. LEFT OUTER JOIN
  267. --Home Primary Address Zipcode COUNT
  268. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.zipcode) AS total
  269. FROM mpi_memhead
  270. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  271. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  272. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  273. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  274. AND mpi_memhead.srcrecno IN (724,672)
  275. AND MPI_PCR_MEMADDR.attrrecno = 107
  276. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  277. GROUP BY mpi_srchead.srccode) AS HOME_PRIMARY_ADDRESS_ZIPCODE ON ALL_SOURCES.srccode = HOME_PRIMARY_ADDRESS_ZIPCODE.srccode
  278. LEFT OUTER JOIN
  279. --Mailing Address Line 1 COUNT
  280. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.stline1) AS total
  281. FROM mpi_memhead
  282. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  283. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  284. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  285. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  286. AND mpi_memhead.srcrecno IN (724,672)
  287. AND MPI_PCR_MEMADDR.attrrecno = 135
  288. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  289. GROUP BY mpi_srchead.srccode) AS MAILING_ADDRESS_LINE1 ON ALL_SOURCES.srccode = MAILING_ADDRESS_LINE1.srccode
  290. LEFT OUTER JOIN
  291. --Mailing Address Line 2 COUNT
  292. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.stline2) AS total
  293. FROM mpi_memhead
  294. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  295. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  296. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  297. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  298. AND mpi_memhead.srcrecno IN (724,672)
  299. AND MPI_PCR_MEMADDR.attrrecno = 135
  300. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  301. GROUP BY mpi_srchead.srccode) AS MAILING_ADDRESS_LINE2 ON ALL_SOURCES.srccode = MAILING_ADDRESS_LINE2.srccode
  302. LEFT OUTER JOIN
  303. --Mailing Address City COUNT
  304. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.city) AS total
  305. FROM mpi_memhead
  306. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  307. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  308. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  309. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  310. AND mpi_memhead.srcrecno IN (724,672)
  311. AND MPI_PCR_MEMADDR.attrrecno = 135
  312. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  313. GROUP BY mpi_srchead.srccode) AS MAILING_ADDRESS_CITY ON ALL_SOURCES.srccode = MAILING_ADDRESS_CITY.srccode
  314. LEFT OUTER JOIN
  315. --Mailing Address State COUNT
  316. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.state) AS total
  317. FROM mpi_memhead
  318. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  319. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  320. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  321. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  322. AND mpi_memhead.srcrecno IN (724,672)
  323. AND MPI_PCR_MEMADDR.attrrecno = 135
  324. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  325. GROUP BY mpi_srchead.srccode) AS MAILING_ADDRESS_STATE ON ALL_SOURCES.srccode = MAILING_ADDRESS_STATE.srccode
  326. LEFT OUTER JOIN
  327. --Mailing Address Country COUNT
  328. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.country) AS total
  329. FROM mpi_memhead
  330. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  331. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  332. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  333. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  334. AND mpi_memhead.srcrecno IN (724,672)
  335. AND MPI_PCR_MEMADDR.attrrecno = 135
  336. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  337. GROUP BY mpi_srchead.srccode) AS MAILING_ADDRESS_COUNTRY ON ALL_SOURCES.srccode = MAILING_ADDRESS_COUNTRY.srccode
  338. LEFT OUTER JOIN
  339. --Mailing Address Zipcode COUNT
  340. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.zipcode) AS total
  341. FROM mpi_memhead
  342. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  343. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  344. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  345. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  346. AND mpi_memhead.srcrecno IN (724,672)
  347. AND MPI_PCR_MEMADDR.attrrecno = 135
  348. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  349. GROUP BY mpi_srchead.srccode) AS MAILING_ADDRESS_ZIPCODE ON ALL_SOURCES.srccode = MAILING_ADDRESS_ZIPCODE.srccode
  350. LEFT OUTER JOIN
  351. --Temp Address Line 1 COUNT
  352. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.stline1) AS total
  353. FROM mpi_memhead
  354. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  355. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  356. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  357. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  358. AND mpi_memhead.srcrecno IN (724,672)
  359. AND MPI_PCR_MEMADDR.attrrecno = 136
  360. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  361. GROUP BY mpi_srchead.srccode) AS TEMP_ADDRESS_LINE1 ON ALL_SOURCES.srccode = TEMP_ADDRESS_LINE1.srccode
  362. LEFT OUTER JOIN
  363. --Temp Address Line 2 COUNT
  364. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.stline2) AS total
  365. FROM mpi_memhead
  366. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  367. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  368. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  369. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  370. AND mpi_memhead.srcrecno IN (724,672)
  371. AND MPI_PCR_MEMADDR.attrrecno = 136
  372. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  373. GROUP BY mpi_srchead.srccode) AS TEMP_ADDRESS_LINE2 ON ALL_SOURCES.srccode = TEMP_ADDRESS_LINE2.srccode
  374. LEFT OUTER JOIN
  375. --Temp Address City COUNT
  376. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.city) AS total
  377. FROM mpi_memhead
  378. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  379. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  380. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  381. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  382. AND mpi_memhead.srcrecno IN (724,672)
  383. AND MPI_PCR_MEMADDR.attrrecno = 136
  384. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  385. GROUP BY mpi_srchead.srccode) AS TEMP_ADDRESS_CITY ON ALL_SOURCES.srccode = TEMP_ADDRESS_CITY.srccode
  386. LEFT OUTER JOIN
  387. --Temp Address State COUNT
  388. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.state) AS total
  389. FROM mpi_memhead
  390. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  391. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  392. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  393. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  394. AND mpi_memhead.srcrecno IN (724,672)
  395. AND MPI_PCR_MEMADDR.attrrecno = 136
  396. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  397. GROUP BY mpi_srchead.srccode) AS TEMP_ADDRESS_STATE ON ALL_SOURCES.srccode = TEMP_ADDRESS_STATE.srccode
  398. LEFT OUTER JOIN
  399. --Temp Address Country COUNT
  400. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.country) AS total
  401. FROM mpi_memhead
  402. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  403. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  404. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  405. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  406. AND mpi_memhead.srcrecno IN (724,672)
  407. AND MPI_PCR_MEMADDR.attrrecno = 136
  408. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  409. GROUP BY mpi_srchead.srccode) AS TEMP_ADDRESS_COUNTRY ON ALL_SOURCES.srccode = TEMP_ADDRESS_COUNTRY.srccode
  410. LEFT OUTER JOIN
  411. --Temp Address Zipcode COUNT
  412. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMADDR.zipcode) AS total
  413. FROM mpi_memhead
  414. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  415. INNER JOIN MPI_PCR_MEMADDR ON MPI_PCR_MEMADDR.memrecno = mpi_memhead.memrecno
  416. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  417. WHERE MPI_PCR_MEMADDR.recstat = 'A'
  418. AND mpi_memhead.srcrecno IN (724,672)
  419. AND MPI_PCR_MEMADDR.attrrecno = 136
  420. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  421. GROUP BY mpi_srchead.srccode) AS TEMP_ADDRESS_ZIPCODE ON ALL_SOURCES.srccode = TEMP_ADDRESS_ZIPCODE.srccode
  422. LEFT OUTER JOIN
  423. --Primary Res Phone AreaCode COUNT
  424. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMPHONE.pharea) AS total
  425. FROM mpi_memhead
  426. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  427. INNER JOIN MPI_PCR_MEMPHONE ON MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
  428. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  429. WHERE MPI_PCR_MEMPHONE.recstat = 'A'
  430. AND mpi_memhead.srcrecno IN (724,672)
  431. AND MPI_PCR_MEMPHONE.attrrecno = 108
  432. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  433. GROUP BY mpi_srchead.srccode) AS PRIM_RES_PHONE_AREA ON ALL_SOURCES.srccode = PRIM_RES_PHONE_AREA.srccode
  434. LEFT OUTER JOIN
  435. --Primary Res Phone Number COUNT
  436. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMPHONE.phnumber) AS total
  437. FROM mpi_memhead
  438. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  439. INNER JOIN MPI_PCR_MEMPHONE ON MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
  440. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  441. WHERE MPI_PCR_MEMPHONE.recstat = 'A'
  442. AND mpi_memhead.srcrecno IN (724,672)
  443. AND MPI_PCR_MEMPHONE.attrrecno = 108
  444. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  445. GROUP BY mpi_srchead.srccode) AS PRIM_RES_PHONE_NUMBER ON ALL_SOURCES.srccode = PRIM_RES_PHONE_NUMBER.srccode
  446. LEFT OUTER JOIN
  447. --Emergency Phone AreaCode COUNT
  448. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMPHONE.pharea) AS total
  449. FROM mpi_memhead
  450. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  451. INNER JOIN MPI_PCR_MEMPHONE ON MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
  452. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  453. WHERE MPI_PCR_MEMPHONE.recstat = 'A'
  454. AND mpi_memhead.srcrecno IN (724,672)
  455. AND MPI_PCR_MEMPHONE.attrrecno = 138
  456. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  457. GROUP BY mpi_srchead.srccode) AS EMERGENCY_PHONE_AREA ON ALL_SOURCES.srccode = EMERGENCY_PHONE_AREA.srccode
  458. LEFT OUTER JOIN
  459. --Emergency Phone Number COUNT
  460. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMPHONE.phnumber) AS total
  461. FROM mpi_memhead
  462. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  463. INNER JOIN MPI_PCR_MEMPHONE ON MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
  464. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  465. WHERE MPI_PCR_MEMPHONE.recstat = 'A'
  466. AND mpi_memhead.srcrecno IN (724,672)
  467. AND MPI_PCR_MEMPHONE.attrrecno = 138
  468. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  469. GROUP BY mpi_srchead.srccode) AS EMERGENCY_PHONE_NUMBER ON ALL_SOURCES.srccode = EMERGENCY_PHONE_NUMBER.srccode
  470. LEFT OUTER JOIN
  471. --Other Phone AreaCode COUNT
  472. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMPHONE.pharea) AS total
  473. FROM mpi_memhead
  474. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  475. INNER JOIN MPI_PCR_MEMPHONE ON MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
  476. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  477. WHERE MPI_PCR_MEMPHONE.recstat = 'A'
  478. AND mpi_memhead.srcrecno IN (724,672)
  479. AND MPI_PCR_MEMPHONE.attrrecno = 109
  480. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  481. GROUP BY mpi_srchead.srccode) AS OTHER_PHONE_AREA ON ALL_SOURCES.srccode = OTHER_PHONE_AREA.srccode
  482. LEFT OUTER JOIN
  483. --Other Phone Number COUNT
  484. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMPHONE.phnumber) AS total
  485. FROM mpi_memhead
  486. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  487. INNER JOIN MPI_PCR_MEMPHONE ON MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
  488. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  489. WHERE MPI_PCR_MEMPHONE.recstat = 'A'
  490. AND mpi_memhead.srcrecno IN (724,672)
  491. AND MPI_PCR_MEMPHONE.attrrecno = 109
  492. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  493. GROUP BY mpi_srchead.srccode) AS OTHER_PHONE_NUMBER ON ALL_SOURCES.srccode = OTHER_PHONE_NUMBER.srccode
  494. LEFT OUTER JOIN
  495. --Business Phone AreaCode COUNT
  496. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMPHONE.pharea) AS total
  497. FROM mpi_memhead
  498. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  499. INNER JOIN MPI_PCR_MEMPHONE ON MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
  500. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  501. WHERE MPI_PCR_MEMPHONE.recstat = 'A'
  502. AND mpi_memhead.srcrecno IN (724,672)
  503. AND MPI_PCR_MEMPHONE.attrrecno = 137
  504. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  505. GROUP BY mpi_srchead.srccode) AS BUSINESS_PHONE_AREA ON ALL_SOURCES.srccode = BUSINESS_PHONE_AREA.srccode
  506. LEFT OUTER JOIN
  507. --Business Phone Number COUNT
  508. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMPHONE.phnumber) AS total
  509. FROM mpi_memhead
  510. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  511. INNER JOIN MPI_PCR_MEMPHONE ON MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
  512. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  513. WHERE MPI_PCR_MEMPHONE.recstat = 'A'
  514. AND mpi_memhead.srcrecno IN (724,672)
  515. AND MPI_PCR_MEMPHONE.attrrecno = 137
  516. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  517. GROUP BY mpi_srchead.srccode) AS BUSINESS_PHONE_NUMBER ON ALL_SOURCES.srccode = BUSINESS_PHONE_NUMBER.srccode
  518. LEFT OUTER JOIN
  519. --Business Phone Extension COUNT
  520. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMPHONE.phextn) AS total
  521. FROM mpi_memhead
  522. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  523. INNER JOIN MPI_PCR_MEMPHONE ON MPI_PCR_MEMPHONE.memrecno = mpi_memhead.memrecno
  524. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  525. WHERE MPI_PCR_MEMPHONE.recstat = 'A'
  526. AND mpi_memhead.srcrecno IN (724,672)
  527. AND MPI_PCR_MEMPHONE.attrrecno = 137
  528. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  529. GROUP BY mpi_srchead.srccode) AS BUSINESS_PHONE_EXTENSION ON ALL_SOURCES.srccode = BUSINESS_PHONE_EXTENSION.srccode
  530. LEFT OUTER JOIN
  531. --Birth date COUNT
  532. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMDATE.memrecno) AS total
  533. FROM mpi_memhead
  534. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  535. INNER JOIN MPI_PCR_MEMDATE ON MPI_PCR_MEMDATE.memrecno = mpi_memhead.memrecno
  536. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  537. WHERE MPI_PCR_MEMDATE.recstat = 'A'
  538. AND mpi_memhead.srcrecno IN (724,672)
  539. AND MPI_PCR_MEMDATE.attrrecno = 122
  540. AND MPI_PCR_MEMDATE.dateval NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'DATE')
  541. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  542. GROUP BY mpi_srchead.srccode) AS BIRTH_DATE ON ALL_SOURCES.srccode = BIRTH_DATE.srccode
  543. LEFT OUTER JOIN
  544. --Death Event date COUNT
  545. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMDATE.memrecno) AS total
  546. FROM mpi_memhead
  547. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  548. INNER JOIN MPI_PCR_MEMDATE ON MPI_PCR_MEMDATE.memrecno = mpi_memhead.memrecno
  549. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  550. WHERE MPI_PCR_MEMDATE.recstat = 'A'
  551. AND mpi_memhead.srcrecno IN (724,672)
  552. AND MPI_PCR_MEMDATE.attrrecno = 128
  553. AND MPI_PCR_MEMDATE.dateval NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'DATE')
  554. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  555. GROUP BY mpi_srchead.srccode) AS DEATH_EVENT_DATE ON ALL_SOURCES.srccode = DEATH_EVENT_DATE.srccode
  556. LEFT OUTER JOIN
  557. --Record Creation date COUNT
  558. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMDATE.memrecno) AS total
  559. FROM mpi_memhead
  560. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  561. INNER JOIN MPI_PCR_MEMDATE ON MPI_PCR_MEMDATE.memrecno = mpi_memhead.memrecno
  562. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  563. WHERE MPI_PCR_MEMDATE.recstat = 'A'
  564. AND mpi_memhead.srcrecno IN (724,672)
  565. AND MPI_PCR_MEMDATE.attrrecno = 124
  566. AND MPI_PCR_MEMDATE.dateval NOT IN (SELECT strval FROM mpi_stranon WHERE strcode = 'DATE')
  567. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  568. GROUP BY mpi_srchead.srccode) AS RECORD_CREATION_DATE ON ALL_SOURCES.srccode = RECORD_CREATION_DATE.srccode
  569. LEFT OUTER JOIN
  570. --CorpID COUNT
  571. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMATTR.memrecno) AS total
  572. FROM mpi_memhead
  573. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  574. INNER JOIN MPI_PCR_MEMATTR ON MPI_PCR_MEMATTR.memrecno = mpi_memhead.memrecno
  575. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  576. WHERE MPI_PCR_MEMATTR.recstat = 'A'
  577. AND mpi_memhead.srcrecno IN (724,672)
  578. AND MPI_PCR_MEMATTR.attrrecno = 133
  579. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  580. GROUP BY mpi_srchead.srccode) AS CORP_ID ON ALL_SOURCES.srccode = CORP_ID.srccode
  581. LEFT OUTER JOIN
  582. --Gender COUNT
  583. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMATTR.memrecno) AS total
  584. FROM mpi_memhead
  585. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  586. INNER JOIN MPI_PCR_MEMATTR ON MPI_PCR_MEMATTR.memrecno = mpi_memhead.memrecno
  587. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  588. WHERE MPI_PCR_MEMATTR.recstat = 'A'
  589. AND mpi_memhead.srcrecno IN (724,672)
  590. AND MPI_PCR_MEMATTR.attrrecno = 123
  591. AND MPI_PCR_MEMATTR.attrval IN ('M','F')
  592. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  593. GROUP BY mpi_srchead.srccode) AS GENDER ON ALL_SOURCES.srccode = GENDER.srccode
  594. LEFT OUTER JOIN
  595. --Confidentially COUNT
  596. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMATTR.memrecno) AS total
  597. FROM mpi_memhead
  598. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  599. INNER JOIN MPI_PCR_MEMATTR ON MPI_PCR_MEMATTR.memrecno = mpi_memhead.memrecno
  600. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  601. WHERE MPI_PCR_MEMATTR.recstat = 'A'
  602. AND mpi_memhead.srcrecno IN (724,672)
  603. AND MPI_PCR_MEMATTR.attrrecno = 129
  604. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  605. GROUP BY mpi_srchead.srccode) AS CONFIDENTIALLY ON ALL_SOURCES.srccode = CONFIDENTIALLY.srccode
  606. LEFT OUTER JOIN
  607. --Death Indicator COUNT
  608. (SELECT mpi_srchead.srccode,COUNT(MPI_PCR_MEMATTR.memrecno) AS total
  609. FROM mpi_memhead
  610. INNER JOIN mpi_srchead ON mpi_srchead.srcrecno = mpi_memhead.srcrecno
  611. INNER JOIN MPI_PCR_MEMATTR ON MPI_PCR_MEMATTR.memrecno = mpi_memhead.memrecno
  612. INNER JOIN mpi_audhead ON mpi_memhead.caudrecno = mpi_audhead.audrecno
  613. WHERE MPI_PCR_MEMATTR.recstat = 'A'
  614. AND mpi_memhead.srcrecno IN (724,672)
  615. AND MPI_PCR_MEMATTR.attrrecno = 127
  616. --and to_char(mpi_audhead.evtctime,'YYYY-MM-DD') > '2013-01-01'
  617. GROUP BY mpi_srchead.srccode) AS DEATH_INDICATOR ON ALL_SOURCES.srccode = DEATH_INDICATOR.srccode;
  618.  
  619.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!