Create a sales view that combines iasWorld, myDec, and CCRD sales

[dfsnow]

The current sales view, default.vw_pin_sale, uses only sales from the iasWorld SALES table. This table is updated on a long lag, and is often missing sales from the tables that feed it (MyDec extracts from the Illinois DOR).

We should create a new experimental sales table that combines sales data from multiple sources, such that a sale will appear in the view if it appears in any of the sources. To start, let's combine the iasWorld SALES table (iasworld.sales) and MyDec (sale.mydec). We should default to using data/information from the SALES table, but if that doesn't exist, we should fallback to MyDec.

Reach goal: We also used to have a separate feed of Cook County Recorder of Deeds (CCRD) sales. Such sales sometimes only appear in the CCRD database, so it would be good to get this feed back. If we do gain access to such a feed, we should combine it with the other two with the priority: iasworld > mydec > ccrd.

The sale view should contain almost all the same columns as the existing default.vw_pin_sale (sales val results, price, date, buyer/seller name, etc.). However, we can drop the extraneous MyDec columns from this version of the view. The sale view should have a new column indicating the source of the sale, one of: iasworld, mydec, or ccrd.

9/12/2024 Update

After discussing with Billy and Dan we are going to take a different direction. We are going to add the mydec data into the existing vw_pin_sale data. We will use coalesce statements to prioritize iasworld data and use an indicator column to let us know where the doc_no was pulled from

[wagnerlmichael]

Are you referring to these extraneous mydec columns?

    mydec_sales.mydec_deed_type,
    mydec_sales.sale_filter_ptax_flag,
    mydec_sales.mydec_property_advertised,
    mydec_sales.mydec_is_installment_contract_fulfilled,
    mydec_sales.mydec_is_sale_between_related_individuals_or_corporate_affiliates, -- noqa
    mydec_sales.mydec_is_transfer_of_less_than_100_percent_interest,
    mydec_sales.mydec_is_court_ordered_sale,
    mydec_sales.mydec_is_sale_in_lieu_of_foreclosure,
    mydec_sales.mydec_is_condemnation,
    mydec_sales.mydec_is_short_sale,
    mydec_sales.mydec_is_bank_reo_real_estate_owned,
    mydec_sales.mydec_is_auction_sale,
    mydec_sales.mydec_is_seller_buyer_a_relocation_company,
    mydec_sales.mydec_is_seller_buyer_a_financial_institution_or_government_agency, -- noqa
    mydec_sales.mydec_is_buyer_a_real_estate_investment_trust,
    mydec_sales.mydec_is_buyer_a_pension_fund,
    mydec_sales.mydec_is_buyer_an_adjacent_property_owner,
    mydec_sales.mydec_is_buyer_exercising_an_option_to_purchase,
    mydec_sales.mydec_is_simultaneous_trade_of_property,
    mydec_sales.mydec_is_sale_leaseback,
    mydec_sales.mydec_is_homestead_exemption,
    mydec_sales.mydec_homestead_exemption_general_alternative,
    mydec_sales.mydec_homestead_exemption_senior_citizens,
    mydec_sales.mydec_homestead_exemption_senior_citizens_assessment_freeze,

[dfsnow]

Are you referring to these extraneous mydec columns?

@wagnerlmichael That's correct.