17,377
社区成员
发帖
与我相关
我的任务
分享
SELECT cso.F_Key, cso.F_ServiceType,cso.F_ASPNode,cso.F_Productline,cso.F_InternalCloseTime,
case WHEN F_InternalCloseTime>nvl(F_ComplaintTime,to_date('1900-01-01','yyyy-mm-dd')) THEN F_InternalCloseTime
ELSE nvl(F_ComplaintTime,to_date('1900-01-01','yyyy-mm-dd')) end MAXTime
FROM cr_cs_cso cso
left join CR_C_RewardPunishRecord rpd on rpd.f_cso = cso.f_key
left join CR_C_RewardPunishCriteria rpc on rpc.f_key = rpd.f_RewardPunishCriteria
WHERE 1=1 and rpd.f_status =50 and rpc.f_code in ('HTF01','HTF02','HTF03','HTF05','HTF06','HTF08','HTF09','HTF10','HTF13')
AND F_Priority IS NOT NULL
AND F_ComplaintType IS NOT NULL
AND F_ComplaintTime IS NOT NULL
AND (F_ComplaintType IN (4003,4006,4007,4008,4005,4009,4019,4034,4030,4029,4018) or F_Priority IN (70,60))
AND cso.F_Status in (160,170,180)
and ((F_InternalCloseTime>isnull(F_ComplaintTime,to_date('1900-01-01','yyyy-mm-dd'))
and F_InternalCloseTime BETWEEN to_date('2012-06-27','yyyy-mm-dd') AND to_date('2012-07-26 23:59:59','yyyy-mm-dd hh24:mi:ss'))
or (
isnull(F_ComplaintTime,'1900-1-1') BETWEEN to_date('2012-06-27','yyyy-mm-dd') AND to_date('2012-07-26 23:59:59','yyyy-mm-dd hh24:mi:ss')
))
and rpd.f_yearmonth='2012-07'
--你的这个语句是错的,不管在mssql还是在oracle,都不能这么用的
AND case WHEN F_InternalCloseTime>isnull(F_ComplaintTime,'1900-1-1') THEN F_InternalCloseTime
ELSE isnull(F_ComplaintTime,'1900-1-1') end BETWEEN '2012-06-27 00:00:00' AND '2012-07-26 23:59:59'