gistfile1.sql

drop table if exists secured_data;
--For secured payments:
SELECT DISTINCT c.id
into temp secured_data

FROM customers c
 JOIN loans l ON c.id = l.customer_id
 JOIN loan_tasks_committed lt on lt.loan_id = l.id
  AND lt.loan_task_cd in ('payoff_loan_task','payoff_instl_task')
 JOIN payment_transactions_committed pt on pt.loan_task_committed_id = lt.id
  AND pt.eff_date = current_date + 3
  AND pt.created_by ilike 'E:%' 
  AND pt.status_cd <> 'cancelled'
 LEFT OUTER JOIN email_log_raw elr ON c.id = elr.customer_id
  AND elr.created_on >= CURRENT_DATE
  AND elr.email_log_type_id = 42

WHERE c.status_cd in ('active','in_default','charged_off')
 AND c.collect_status_cd not in ('bankruptcy','cease_and_desist','deployed_military','deceased','fraud','legal','credit_counseling','promise_western_union','promise_moneygram','promise_money_order','promise_check')
 AND elr.id IS NULL
;

--For unsecured payments:
drop table if exists unsecured_data;
SELECT DISTINCT c.id
into temp unsecured_data

FROM customers c
 JOIN loans l ON c.id = l.customer_id
 LEFT OUTER JOIN email_log_raw elr ON c.id = elr.customer_id
  AND elr.created_on >= CURRENT_DATE
  AND elr.email_log_type_id = 42

WHERE c.status_cd in ('active','in_default','charged_off')
 AND c.collect_status_cd in ('promise_western_union','promise_moneygram','promise_money_order','promise_check')
 AND elr.id IS NULL
 AND c.collect_next_action_date = current_date + 3
 ;

SELECT * FROM secured_data
UNION SELECT * FROM unsecured_data