gistfile1.txt

get_p_id <- function(p_id, connection) {
    sqlQuery(connection, sprintf(    
                "select p.id 'proposal_id',
                        pd.id 'proposal_detail_id',
                        mm.media_month,
                        b.name 'MVPD',             
                        n.code 'network',
                        mpi.post_log_recieved_date 'log_date',
                        pd.start_date 'detail_start',
                        pd.end_date 'detail_end',
                        mpi.week_1,
                        mpi.week_2,
                        mpi.week_3,
                        mpi.week_4,
                        mpi.week_5,
                        mpi.m_f_10a_11a,
                        mpi.m_f_10p_11p,
                        mpi.m_f_11a_12p,
                        mpi.m_f_11p_12p,
                        mpi.m_f_12a_1a,
                        mpi.m_f_12p_1p,
                        mpi.m_f_1a_2a,
                        mpi.m_f_1p_2p,
                        mpi.m_f_2a_3a,
                        mpi.m_f_2p_3p,
                        mpi.m_f_3a_4a,
                        mpi.m_f_3p_4p,
                        mpi.m_f_4a_5a,
                        mpi.m_f_4p_5p,
                        mpi.m_f_5a_6a,
                        mpi.m_f_5p_6p,
                        mpi.m_f_6a_7a,
                        mpi.m_f_6p_7p,
                        mpi.m_f_7a_8a,
                        mpi.m_f_7p_8p,
                        mpi.m_f_8a_9a,
                        mpi.m_f_8p_9p,
                        mpi.m_f_9a_10a,
                        mpi.m_f_9p_10p,
                        mpi.sa_su_10a_11a,
                        mpi.sa_su_10p_11p,
                        mpi.sa_su_11a_12p,
                        mpi.sa_su_11p_12p,
                        mpi.sa_su_12a_1a,
                        mpi.sa_su_12p_1p,
                        mpi.sa_su_1a_2a,
                        mpi.sa_su_1p_2p,
                        mpi.sa_su_2a_3a,
                        mpi.sa_su_2p_3p,
                        mpi.sa_su_3a_4a,
                        mpi.sa_su_3p_4p,
                        mpi.sa_su_4a_5a,
                        mpi.sa_su_4p_5p,
                        mpi.sa_su_5a_6a,
                        mpi.sa_su_5p_6p,
                        mpi.sa_su_6a_7a,
                        mpi.sa_su_6p_7p,
                        mpi.sa_su_7a_8a,
                        mpi.sa_su_7p_8p,
                        mpi.sa_su_8a_9a,
                        mpi.sa_su_8p_9p,
                        mpi.sa_su_9a_10a,
                        mpi.sa_su_9p_10p,                        
                        mpi.contracted_hh_cpm,
                        mpi.contracted_hh_impressions,
                        mpi.post_log_hh_impressions,
                        mpi.affidavit_hh_impressions 
                       
                            from mart.pacing_inputs3 mpi
                               join media_months mm on mm.id=mpi.media_month_id
                               join businesses b on b.id = mpi.business_id
                               join proposals p on p.id=mpi.proposal_id
                               join proposal_details pd on pd.id = mpi.proposal_detail_id
                               join networks n on n.id=pd.network_id 
                        WHERE mpi.proposal_id = %d
                        ", p_id))

}

p_ids <- sqlQuery(connection, "SELECT DISTINCT proposal_id FROM mart.pacing_inputs3")

D <- p_ids %>% map_df(get_p_id, connection=connection)