Skip to content

Instantly share code, notes, and snippets.

@vsizov

vsizov/gist:8956013

Created February 12, 2014 13:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save vsizov/8956013 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save vsizov/8956013 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. vsizov created this gist Feb 12, 2014.
    41 changes: 41 additions & 0 deletions gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,41 @@
    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