CASE WHEN IS NULL THEN ELSE.

SELECT T1.BIRTHEMP,
  T1.BIRTHEMPDEP,
  (
  CASE
    WHEN T2.BLESSNUM IS NULL
    THEN 0
    ELSE T2.BLESSNUM
  END) AS BLESSNUM
FROM
  (SELECT E.EMP_NAME AS BIRTHEMP,
    M.NAME           AS BIRTHEMPDEP,
    E.USER_ID
  FROM M_EMPLOYEE E
  LEFT JOIN M_ORGANIZATION M
  ON M.ORG_ID                   =E.DEPT_ID
  WHERE TO_CHAR(E.BIRTHDAY,'MM')=TO_CHAR(SYSDATE,'MM')
  )T1
LEFT JOIN
  (SELECT TO_USER,
    COUNT(*) AS blessnum
  FROM T_ECM_BIRTH_BLESS
  LEFT JOIN M_EMPLOYEE M
  ON M.USER_ID=TO_USER
  GROUP BY TO_USER
  )T2 ON T1.USER_ID=T2.TO_USER

SELECT pjt.NAME, tsu.TRUENAME, tsu.USERNAME, -- 1月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 1 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 1 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 1 THEN 1 ELSE 0 END), 2) END AS JAN_RATE, -- 2月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 2 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 2 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 2 THEN 1 ELSE 0 END), 2) END AS FEB_RATE, -- 3月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 3 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 3 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 3 THEN 1 ELSE 0 END), 2) END AS MAR_RATE, -- 4月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 4 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 4 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 4 THEN 1 ELSE 0 END), 2) END AS MAR_RATE, -- 5月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 5 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 5 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 5 THEN 1 ELSE 0 END), 2) END AS MAR_RATE, -- 6月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 6 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 6 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 6 THEN 1 ELSE 0 END), 2) END AS MAR_RATE, -- 7月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 7 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 7 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 7 THEN 1 ELSE 0 END), 2) END AS MAR_RATE, -- 8月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 8 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 8 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 8 THEN 1 ELSE 0 END), 2) END AS MAR_RATE, -- 9月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 9 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 9 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 9 THEN 1 ELSE 0 END), 2) END AS MAR_RATE, -- 10月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 10 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 10 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 10 THEN 1 ELSE 0 END), 2) END AS MAR_RATE, -- 11月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 11 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 11 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 11 THEN 1 ELSE 0 END), 2) END AS MAR_RATE, -- 12月达成率 CASE WHEN SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 12 THEN 1 ELSE 0 END) = 0 THEN '/' ELSE ROUND(SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 12 AND tsk.REALENDDATE IS NOT NULL AND tsk.REALENDDATE <= tsk.PLANENDDATE THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN MONTH(tsk.PLANENDDATE) = 12 THEN 1 ELSE 0 END), 2) END AS MAR_RATE FROM t_psm_pjt pjt LEFT JOIN t_tsk_tsk tsk ON tsk.OBJECTID = pjt.ID left join t_sys_user tsu on tsu.ID = pjt.MANAGERID WHERE pjt.TYPEID = 'ef22569a-9bac-455b-950f-934a94b4b00b' -- 新技术新平台项目模板 AND YEAR(tsk.PLANENDDATE) = YEAR(NOW()) -- 只查询当前年份 AND tsk.ISNODE = 'N' AND tsk.ISVALID = 'Y' GROUP BY pjt.NAME, pjt.MANAGERID 这个也给我写一个总计
10-14
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值