#### 引用自:王沛弘
#### 根据表内学生编号,拉取下沟通详情
#### 执行时间:0.12s,shape(274,3),新脱敏库hfjy
#### 日期:2018-12-13
#### 需求方:庄辉
#### 数据输出方式:excel
#### created by:汪国强
#### update by:汪国强
#### checked by:

SELECT
    s.student_no 学员编号,
    s.name 学生姓名,
    (select name from view_user_info u where u.user_id = cr.communication_person) 沟通人,
    cr.role_code 沟通角色,
    (case when sale_stage2 = 0 then '新名单' when sale_stage2 = 1 then '跟进中' when sale_stage2 = 2 
    then '无效名单' when sale_stage2 = 3 then '未接听' when sale_stage2 = 4 then '未试听失单'
    when sale_stage2 = 5 then '工作台销售阶段' when sale_stage2 = 6 then '已发起设班单' 
    when sale_stage2 = 7 then '已驳回或撤回' when sale_stage2 = 8 then '已排待沟通'
    when sale_stage2 = 9 then '试听后待反馈' when sale_stage2 = 10 then '反馈后跟进中' 
    when sale_stage2 = 11 then '试听已跳票或取消' when sale_stage2 = 12 then '试听环节失单'
    when sale_stage2 = 13 then '待提交成单' when sale_stage2 = 14 then '成单签订中' when sale_stage2 = 15 then '已成单' end) '销售阶段',
    cr.content 沟通内容,
    cr.start_time 沟通开始时间,
    s.create_time 注册时间,
    ad.adid_no 'ADID',
    (select value from ddic where type = 'TP056' and code =cr.refuse_reason) 拒绝原因
FROM 
    view_student s
    left join view_communication_record cr on cr.student_intention_id = s.student_intention_id
    left join adid ad on ad.adid_id = s.adid_id
    left join lesson_plan lp on s.student_id = lp.student_id 
WHERE 
    s.student_no in    tc.contract_id in ()
文档更新时间: 2019-01-16 16:57   作者:admin