oracle sql 日期 case when where

最新推荐文章于 2024-09-02 06:00:00 发布

Y_JY

最新推荐文章于 2024-09-02 06:00:00 发布

阅读量2.1k

点赞数

分类专栏： oracle

oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

public String getMarkets(CrmTransBean crmTransBean) throws AppException{
StringBuilder sb = new StringBuilder();
sb.append("SELECT CA.ID, ");
sb.append(" CA.NAME, ");
sb.append(" TO_CHAR( CA.START_C,'yyyy-MM') MONDATE, ");
sb.append(" CC.NAME CREATENAME, ");
//活动开始时间转换
sb.append(" CASE ");
sb.append(" WHEN 1=1 ");
sb.append(" THEN REPLACE(TO_CHAR( CA.START_C,'mm-dd pmhh24:mi:ss'),'am','上午') ");
sb.append(" WHEN 1=1 ");
sb.append(" THEN REPLACE(TO_CHAR( CA.START_C,'mm-dd pmhh24:mi:ss'),'pm','下午') ");
sb.append(" ELSE '未填写' ");
sb.append(" END STARTTIME, ");
//活动结束时间转换
sb.append(" CASE ");
sb.append(" WHEN 1=1 ");
sb.append(" THEN REPLACE(TO_CHAR( CA.END_C,'mm-dd pmhh24:mi:ss'),'am','上午') ");
sb.append(" WHEN 1=1 ");
sb.append(" THEN REPLACE(TO_CHAR( CA.END_C,'mm-dd pmhh24:mi:ss'),'pm','下午') ");
sb.append(" ELSE '未填写' ");
sb.append(" END ENDTIME, ");
//预约开始时间转换
sb.append(" CASE ");
sb.append(" WHEN 1=1 ");
sb.append(" THEN REPLACE(TO_CHAR( CA.OPENDATE,'mm-dd pmhh24:mi:ss'),'am','上午') ");
sb.append(" WHEN 1=1 ");
sb.append(" THEN REPLACE(TO_CHAR( CA.OPENDATE,'mm-dd pmhh24:mi:ss'),'pm','下午') ");
sb.append(" ELSE '未填写' ");
sb.append(" END OPENTIME, ");
//预约结束时间转换
sb.append(" CASE ");
sb.append(" WHEN 1=1 ");
sb.append(" THEN REPLACE(TO_CHAR( CA.CLOSEDDATE,'mm-dd pmhh24:mi:ss'),'am','上午') ");
sb.append(" WHEN 1=1 ");
sb.append(" THEN REPLACE(TO_CHAR( CA.CLOSEDDATE,'mm-dd pmhh24:mi:ss'),'pm','下午') ");
sb.append(" ELSE '未填写' ");
sb.append(" END CLOSETIME, ");

sb.append(" CASE ");
sb.append(" WHEN CA.SORT_C = '年度钻石年会' OR CA.SORT_C = 'PE类合伙人年会' ");
sb.append(" THEN 'Y' ELSE 'N' ");
sb.append(" END FC_CANBOOK, ");
sb.append(" CASE ");
sb.append(" WHEN CA.END_C < SYSDATE ");
sb.append(" THEN '已结束' ELSE '未结束' ");
sb.append(" END STATE, ");
sb.append(" CASE ");
sb.append(" WHEN CA.CREATEDATE > SYSDATE-7 ");
sb.append(" THEN 'Y' ELSE 'N' ");
sb.append(" END ISNEW, ");
sb.append(" CASE ");
sb.append(" WHEN CA.END_C > SYSDATE AND CA.CLOSEDDATE > SYSDATE ");
sb.append(" THEN 'Y' ELSE 'N' ");
sb.append(" END ISFIRST ");
sb.append(" FROM CAMPAIGN CA ");
sb.append(" JOIN CCUSER CC ");
sb.append(" ON CA.CREATEBYID = CC.ID ");
sb.append(" WHERE 1=1 ");
sb.append(" AND (( ");
sb.append(" CC.NAME != '数据交互用户' AND CA.ZHUANGTAI IN('已完成','总部市场部批准','客服项目经理批准','活动结果已通过审核','活动已提交,但审核未通过','未开始','进行中','开放预约','结束预约') ");
sb.append(" AND CA.START_C IS NOT NULL AND CA.END_C IS NOT NULL AND CA.PITCH_AREA IS NOT NULL ");
sb.append(" AND (CA.RANGE_NEW LIKE '%"+crmTransBean.getBranchname()+"%' OR CA.RANGE_NEW LIKE '%全国%') ");
sb.append(" ) ");
sb.append(" OR ( ");
sb.append(" CC.NAME = '数据交互用户' AND CA.PUBLISH_STATUS = '已发布' AND CA.ZHUANGTAI != '活动无效' ");
sb.append(" AND ( CA.BRANCH LIKE '%"+crmTransBean.getBranchname()+"%' OR CA.BRANCH LIKE '%全国%') ");
sb.append(" )) ");
if(!"".equals(crmTransBean.getSearch()) && null != crmTransBean.getSearch()){
sb.append(" AND CA.NAME LIKE '%"+crmTransBean.getSearch()+"%' ");
}
if(null != crmTransBean.getIsfirst() && crmTransBean.getIsfirst().equals("Y")){
sb.append(" AND (CASE ");
sb.append(" WHEN CA.END_C > SYSDATE AND CA.CLOSEDDATE > SYSDATE ");
sb.append(" THEN 'Y' ELSE 'N' ");
sb.append(" END )='Y' ");
}
if(null != crmTransBean.getIsfirst() && crmTransBean.getIsfirst().equals("N")){
sb.append(" AND (CASE ");
sb.append(" WHEN CA.END_C > SYSDATE AND CA.CLOSEDDATE > SYSDATE ");
sb.append(" THEN 'Y' ELSE 'N' ");
sb.append(" END )='N' ");
}
if(null != crmTransBean.getMondate1() && !"".equals(crmTransBean.getMondate1()) && !"".equals(crmTransBean.getMondate2()) && null != crmTransBean.getMondate2()){
sb.append(" AND TO_CHAR(CA.START_C, 'yyyy-MM') IN ('"+crmTransBean.getMondate1()+"','"+crmTransBean.getMondate2()+"') ");
}
if(null != crmTransBean.getMondate1() && !"".equals(crmTransBean.getMondate1()) && ("".equals(crmTransBean.getMondate2()) || null == crmTransBean.getMondate2())){
sb.append(" AND TO_CHAR(CA.START_C, 'yyyy-MM') IN ('"+crmTransBean.getMondate1()+"') ");
}
sb.append("ORDER BY START_C DESC");
return sb.toString();
}