ORACLE求大神帮我解决一下sql

[一丁丁点的爆炸头]

SELECT SUBSTR(CRTDATE, 1, 10), NOTPERFECT_USER, AVG(EVERYDAY) EVERYDAY
  FROM (SELECT AP.CRTDATE,
               CASE
                 WHEN AP.NOTPERFECT_USER IS NOT NULL THEN
                  AP.NOTPERFECT_USER
                 ELSE
                  AP.OVERBOOKINGUSER
               END NOTPERFECT_USER,
               CASE
                 WHEN NOTPERFECT_TIME IS NOT NULL THEN
                  (SELECT (TO_DATE(AP.NOTPERFECT_TIME, 'yyyy-mm-dd hh24:mi:ss') -
                          TO_DATE(AP.CRTDATE, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60
                     FROM DUAL)
                 ELSE
                  (SELECT (TO_DATE(AP.OVERBOOKINGDATE, 'yyyy-mm-dd hh24:mi:ss') -
                          TO_DATE(AP.CRTDATE, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60
                     FROM DUAL)
               END EVERYDAY
          FROM EXP_BOOKAPD AP
         WHERE AP.RECSTS = '1')
 GROUP BY NOTPERFECT_USER, SUBSTR(CRTDATE, 1, 10)
 ORDER BY NOTPERFECT_USER, SUBSTR(CRTDATE, 1, 10);

这个是我获取2个时间相减后的分组的平均值显示出来，现在想做的是AP.NOTPERFECT_TIME和AP.OVERBOOKINGDATE满足以下逻辑，只算工作时间。。排除节假日。如果是工作日的。从上午9点到11点半。从下午1点到下午5点半算工作日。比如上传的时间是11点30.打单员下午1点1分打的单子。那么这票的打单用时就是1分钟。

[js14982]

报什么错？ 还有测试数据给点

[js14982]

case when (substr(AP.NOTPERFECT_TIME,12,5) between 09:00 and 11:30) and (substr(AP.CRTDATE,12,5) between 13:00 and 17:30) then TO_DATE(AP.NOTPERFECT_TIME, 'yyyy-mm-dd hh24:mi:ss') - TO_DATE(AP.CRTDATE, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 - 90

[一丁丁点的爆炸头]

我的意思是当上面的2个字段是工作日并且是9：00-11.30下午13:00到17:30才会进行想减的操作，然后相减的时候 如果是(AP.NOTPERFECT_TIME是11:30，AP.CRTDATE是13:01那相减的时候就要是1分钟。这边是我刚写的代码不知道对不对SELECT SUBSTR(CRTDATE, 1, 10), NOTPERFECT_USER, AVG(EVERYDAY) EVERYDAY
FROM (SELECT AP.CRTDATE,
CASE
WHEN AP.NOTPERFECT_USER IS NOT NULL THEN
AP.NOTPERFECT_USER
ELSE
AP.OVERBOOKINGUSER
END NOTPERFECT_USER,
CASE
WHEN NOTPERFECT_TIME IS NOT NULL THEN
CASE
WHEN (SELECT COUNT(*)
FROM HOLIDAY H
WHERE H.HL_DATE =
(TO_DATE(AP.NOTPERFECT_TIME, 'yyyy-mm-dd'))) = 0 AND
((TO_DATE(AP.NOTPERFECT_TIME, 'hh24:mi:ss') BETWEEN
'09:00:00' AND '11:30:00') OR
(TO_DATE(AP.NOTPERFECT_TIME, 'hh24:mi:ss') BETWEEN
'13:00:00' AND '17:30:00')) THEN
(SELECT (TO_DATE(AP.NOTPERFECT_TIME, 'yyyy-mm-dd hh24:mi:ss') -
TO_DATE(AP.CRTDATE, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60
FROM DUAL)
END
ELSE (SELECT (TO_DATE(AP.OVERBOOKINGDATE,
'yyyy-mm-dd hh24:mi:ss') -
TO_DATE(AP.CRTDATE, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60
FROM DUAL) END EVERYDAY
FROM EXP_BOOKAPD AP
WHERE AP.RECSTS = '1')
GROUP BY NOTPERFECT_USER, SUBSTR(CRTDATE, 1, 10)
ORDER BY NOTPERFECT_USER, SUBSTR(CRTDATE, 1, 10);

[一丁丁点的爆炸头]

其中的 CASE
WHEN (SELECT COUNT(*)
FROM HOLIDAY H
WHERE H.HL_DATE =
(TO_DATE(AP.NOTPERFECT_TIME, 'yyyy-mm-dd'))) = 0 holiday中存的是2016年所有的假日包括周末个调休的日期。

[一丁丁点的爆炸头]

其中AP.NOTPERFECT_TIME和AP.OVERBOOKINGDATE就是打单的时间，一个为Null的话就去另外一个 crtdate就是创建的时间 。创建的时间一定比打单的时间小。比如AP.NOTPERFECT_TIME不为null ，值为2016-11-28 15：30:26操作过 ，创建的时间是2016-11-28 15:15:00 这个时间。

[js14982]

首先，你的TO_DATE(AP.NOTPERFECT_TIME, 'yyyy-mm-dd') 和你TO_DATE(AP.NOTPERFECT_TIME, 'hh24:mi:ss') 这些都不对，你to_date转换成日期格式，无论怎样格式都是 年-月-日-小时-分-秒，不会只显示你要的时分秒，你可能需要再转成to_char

[js14982]

第二，你的标量子查询肯定用的不对，你这个应该会报一个，单行返回多行值的错误。

[一丁丁点的爆炸头]

我转换成to_char后包的错误是ORA-01830: 日期格式图片在转换整个输入字符串之前结"

[一丁丁点的爆炸头]

方便留下您的QQ吗？我加您讨论下。

[js14982]

qq私信给你了，另外你的表结构和日期数据格式说明一下

[js14982]

SELECT SUBSTR(CRTDATE, 1, 10), NOTPERFECT_USER, AVG(EVERYDAY) EVERYDAY
  FROM (SELECT AP.CRTDATE,
               nvl(AP.NOTPERFECT_USER,AP.OVERBOOKINGUSER) NOTPERFECT_USER,
               case when nvl(AP.NOTPERFECT_USER,AP.OVERBOOKINGUSER) not in (select HL_DATE FROM HOLIDAY)
                     and (substr(AP.NOTPERFECT_TIME,12,5) between 09:00 and 11:30) 
                     and (substr(AP.CRTDATE,12,5) between 13:00 and 17:30)
               then 
TO_DATE(nvl(AP.NOTPERFECT_USER,AP.OVERBOOKINGUSER), 'yyyy-mm-dd hh24:mi:ss') - TO_DATE(AP.CRTDATE, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 - 90
               else 
               TO_DATE(nvl(AP.NOTPERFECT_USER,AP.OVERBOOKINGUSER), 'yyyy-mm-dd hh24:mi:ss') - TO_DATE(AP.CRTDATE, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60
             end EVERYDAY
          FROM EXP_BOOKAPD AP
         WHERE AP.RECSTS = '1')
 GROUP BY NOTPERFECT_USER, SUBSTR(CRTDATE, 1, 10)
 ORDER BY NOTPERFECT_USER, SUBSTR(CRTDATE, 1, 10);

这个语句你先看一下，nvl(AP.NOTPERFECT_USER,AP.OVERBOOKINGUSER) not in (select HL_DATE FROM HOLIDAY)这个地方可能需要转格式，否则匹配不到数据的

[一丁丁点的爆炸头]

你私信的QQ是我注册的名字 我的QQ：459787120 你加我下 另外我运行了一下你刚才给我的代码 between这个地方缺少关键字

[js14982]

加你了看到没，报错加引号between '09:00' and '11:30'