人员销售业绩

                  SELECT cp.khmc 客户名称,
                         CASE
                           WHEN CP.KHH IS NOT NULL THEN
                            (SELECT CASE
                                      WHEN KH.KHLX = 0 THEN
                                       '个人'
                                      WHEN KH.JGKHSF = 2 THEN
                                       '金融机构'
                                      ELSE
                                       '机构'
                                    END
                               FROM TKHXX KH
                              WHERE KH.ID = CP.KHH)
                           ELSE
                            (SELECT CASE
                                      WHEN TQZ.KHLX = 0 THEN
                                       '个人'
                                      ELSE
                                       '机构'
                                    END
                               FROM TQZKHXX TQZ
                              WHERE TQZ.ID = CP.QZKHID)
                         END 客户类型,
                         (SELECT ry.ryxm FROM tryxx ry where ry.id = cp.ryxx) 客户经理,
                         (SELECT (SELECT TD.TDMC FROM TTDBM TD WHERE TD.ID = RY.TD)
                            FROM TRYXX RY
                           WHERE RY.ID = CP.RYXX) 团队名称,
                         cpdm.cpqc 产品名称,
                         cpdm.cpmc 期数,
                         case
                           WHEN cpdm.cplx = 1 THEN
                            '集合信托产品'
                           WHEN cpdm.cplx = 2 THEN
                            '资金池产品'
                         END 产品类型,
                         to_char(to_date(cpdm.clrq, 'yyyymmdd'), 'yyyy-mm-dd') 成立日期,
                         zj.jzj 交易金额,
                         case
                           when cpdm.cplx = 1 then
                            '1'
                           else
                            to_char((case
                                      when cpqx.xmqx = 1 then
                                       round(cpqx.cpqx / 365, 7)
                                      when cpqx.xmqx = 2 then
                                       round(cpqx.cpqx * 30 / 365, 7)
                                      when cpqx.xmqx = 3 then
                                       round(cpqx.cpqx)
                                    end),
                                    'fm990.0000000')
                         end 业绩系数,
                         case
                           when cpdm.cplx = 1 then
                            zj.jzj
                           else
                           
                            (case
                              when cpqx.xmqx = 1 then
                               zj.jzj * round(cpqx.cpqx / 365, 7)
                              when cpqx.xmqx = 2 then
                               zj.jzj * round(cpqx.cpqx * 30 / 365, 7)
                              when cpqx.xmqx = 3 then
                               zj.jzj * cpqx.cpqx
                            end)
                         end 核算规模
                    FROM tcpyy cp,
                         tfp_cpdm cpdm,
                         txsxm_cpqx cpqx,
                         (SELECT zj.cpyyid, sum(zj.jzje) jzj
                            FROM tzjjz zj
                           WHERE zj.zjzt = 1
                             AND zj.shzt = 2
                             AND zj.zjqr = 1
                           GROUP BY zj.cpyyid) zj
                   WHERE cp.id = zj.cpyyid
                     AND cp.cpid = cpdm.id
                     AND cp.yyzt = 2
                     and cp.cpqx = cpqx.id
                     AND cp.yygmje = zj.jzj
                     AND CPDM.CLRQ >= 20191101
                     AND CPDM.CLRQ <= 20191231
                  union all
                  select null 客户名称,
                         null 客户类型,
                         null 客户经理,
                         null 团队名称,
                         null 产品名称,
                         '合计' 期数,
                         null 产品类型,
                         null 成立日期,
                         sum(zj.jzj) 交易金额,
                         null 业绩系数,
                         round(sum(case
                                     when cpdm.cplx = 1 then
                                      zj.jzj
                                     else
                                      (case
                                        when cpqx.xmqx = 1 then
                                         zj.jzj * round(cpqx.cpqx / 365, 7)
                                        when cpqx.xmqx = 2 then
                                         zj.jzj * round(cpqx.cpqx * 30 / 365, 7)
                                        when cpqx.xmqx = 3 then
                                         zj.jzj * cpqx.cpqx
                                      end)
                                   end)) 核算规模
                    FROM tcpyy cp,
                         tfp_cpdm cpdm,
                         txsxm_cpqx cpqx,
                         (SELECT zj.cpyyid, sum(zj.jzje) jzj
                            FROM tzjjz zj
                           WHERE zj.zjzt = 1
                             AND zj.shzt = 2
                             AND zj.zjqr = 1
                           GROUP BY zj.cpyyid) zj
                   WHERE cp.id = zj.cpyyid
                     AND cp.cpid = cpdm.id
                     and cp.cpqx = cpqx.id
                     AND cp.yyzt = 2
                     AND cp.yygmje = zj.jzj
                     AND CPDM.CLRQ >= 20191101
                     AND CPDM.CLRQ <= 20191231