close channel

close_channel.md

the close channel is derived in the following sub query

left outer join 
    ( select distinct new_opportunityid
      from stg.v_contractactions ca 
        join stg.v_tbltransmaster tm on ca.vcmembertransnumber=tm.vctransactionnumber
          and ca.source_system_id=tm.source_system_id
        join stg.esp_new_contractactions eca on ca.icontractid=eca.new_name
        join stg.bs_tbl_facility b on b.code=eca.new_vchomefacilityid
           and b.companyid=ca.company_id
      where new='y' and vcTransactionAtFacilityID in ('998','996','995','994')) clc on clc.new_opportunityid=o.new_opportunityid

and here is the case statement,

 case 
    when o.company_id=3  then --blink
      case 
        when  o.Opportunity_Status <>'won' then  'Unknown'
        when bc.vcmemberid is not null and jointype not in ('AtHome','online') then '-6' --In-Club Online 
        when bc.vcmemberid is not null and jointype in ('AtHome','online') then 'CC586C0B-44B1-DF11-9988-001372F9C247' --Online 
        --when vccreatedterminalid like '%kiosk%' then 'Kiosk'
        when clc.new_opportunityid is not null then 'E8C52512-44B1-DF11-9988-001372F9C247'--Kiosk
        else '605D3231-81C2-DB11-981E-001372F9C247' end --eClub
    else
      case
        when  o.Opportunity_Status <>'won' then  'Unknown'
        when clc.new_opportunityid is not null then 'AD0AFAAF-8868-DD11-A0BF-001372F9C247'--web
        when  o.Opportunity_StatuS ='won'  then  'B9147529-81C2-DB11-981E-001372F9C247'--esp
        else 'Unknown'  end 
    end as Close_Data_Source_Code