Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT DISTINCT
- op.id AS opportunity_id
- , accountid
- , zendesk_id__c
- , subdomain__c
- , account_name__c
- , account_region__c
- , business_unit__c
- , self_service_touched__c
- , type
- , type_of_expansion__c
- , non_commissionable__c
- , bypass_finance__c
- , finance_approved__c
- , reviewed_by_finance__c
- , stagename
- , op.createddate
- , op.createdbyid
- , term_start__c
- , closedate
- , term__c
- , finance_contract_type__c
- , introductory_omnichannel_promotion__c
- , has_suite__c
- , zd_plan__c, support_addons__c, chat_plan__c, guide_plan__c, talk_plan__c, connect_plan__c
- , of_agents__c, chat_number_of_agents__c, guide_number_of_agents__c, talk_no_of_agents__c, connect_mmus__c
- , support_plan_mrr__c, support_add_on_mrr__c, chat_mrr__c, guide_mrr__c, talk_mrr__c, connect_mrr__c
- , total_support_plan_mrr__c, total_support_add_on_mrr__c, total_chat_mrr__c, total_guide_mrr__c, total_talk_mrr__c, total_connect_mrr__c
- , booking_mrr__c
- , total_commissionable_mrr__c
- , amount
- , arr__c
- , currencyisocode
- , payment_method__c
- , initiated_by__c
- , payment_terms__c
- , ownerid
- , owner_actual_name__c
- , owner_actual_role__c
- , op.dw_eff_start, op.dw_eff_end, op.dw_curr_ind
- , ROUND(amount / conversionrate,0) AS amount_usd
- , ROUND(arr__c / conversionrate,0) AS arr_usd
- , ROUND(booking_mrr__c / conversionrate,0) AS booking_mrr_usd
- , ROUND(chat_mrr__c / conversionrate,0) AS chat_mrr_usd
- , ROUND(guide_mrr__c / conversionrate,0) AS guide_mrr_usd
- , ROUND(support_add_on_mrr__c / conversionrate,0) AS support_add_on_mrr_usd
- , ROUND(support_plan_mrr__c / conversionrate,0) AS support_plan_mrr_usd
- , ROUND(talk_adjustments__c / conversionrate,0) AS talk_adjustments_usd
- , ROUND(talk_mrr__c / conversionrate,0) AS talk_mrr_usd
- , ROUND(total_bime_mrr__c / conversionrate,0) AS total_bime_mrr_usd
- , ROUND(total_chat_mrr__c / conversionrate,0) AS total_chat_mrr_usd
- , ROUND(total_commissionable_mrr__c / conversionrate,0) AS total_commissionable_mrr_usd
- , ROUND(total_connect_mrr__c / conversionrate,0) AS total_connect_mrr_usd
- , ROUND(total_explore_mrr__c / conversionrate,0) AS total_explore_mrr_usd
- , ROUND(total_guide_mrr__c / conversionrate,0) AS total_guide_mrr_usd
- , ROUND(total_message_mrr__c / conversionrate,0) AS total_message_mrr_usd
- , ROUND(total_support_add_on_mrr__c / conversionrate,0) AS total_support_add_on_mrr_usd
- , ROUND(total_support_mrr__c / conversionrate,0) AS total_support_mrr_usd
- , ROUND(total_support_plan_mrr__c / conversionrate,0) AS total_support_plan_mrr_usd
- , ROUND(total_talk_mrr__c / conversionrate,0) AS total_talk_mrr_usd
- FROM `edw-prod-153420.sfdc.opp_scd2` op
- LEFT JOIN `edw-prod-153420.sfdc.datedconvrate_scd2` cr
- ON cr.dw_curr_ind = 'Y'
- AND op.currencyisocode = cr.isocode
- AND op.closedate >= cr.startdate
- AND op.closedate < cr.nextstartdate
- WHERE op.dw_curr_ind = "Y"
- AND op.createddate >= '2014-01-01'
Add Comment
Please, Sign In to add comment
