ORACLE求大神帮我解决一下sql

一丁丁点的爆炸头 菜鸡  2016-11-29 01:46:14
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分钟。
...全文
129 点赞 收藏 13
写回复
13 条回复
js14982 2016年11月29日
加你了看到没,报错加引号between '09:00' and '11:30'
回复 点赞
一丁丁点的爆炸头 2016年11月29日
你私信的QQ是我注册的名字 我的QQ:459787120 你加我下 另外我运行了一下你刚才给我的代码 between这个地方缺少关键字
回复 点赞
js14982 2016年11月29日
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)这个地方可能需要转格式,否则匹配不到数据的
回复 点赞
js14982 2016年11月29日
qq私信给你了,另外你的表结构和日期数据格式说明一下
回复 点赞
一丁丁点的爆炸头 2016年11月29日
方便留下您的QQ吗?我加您讨论下。
回复 点赞
一丁丁点的爆炸头 2016年11月29日
我转换成to_char后包的错误是ORA-01830: 日期格式图片在转换整个输入字符串之前结"
回复 点赞
js14982 2016年11月29日
第二,你的标量子查询肯定用的不对,你这个应该会报一个,单行返回多行值的错误。
回复 点赞
js14982 2016年11月29日
首先,你的TO_DATE(AP.NOTPERFECT_TIME, 'yyyy-mm-dd') 和你TO_DATE(AP.NOTPERFECT_TIME, 'hh24:mi:ss') 这些都不对,你to_date转换成日期格式,无论怎样格式都是 年-月-日-小时-分-秒,不会只显示你要的时分秒,你可能需要再转成to_char
回复 点赞
一丁丁点的爆炸头 2016年11月29日
其中AP.NOTPERFECT_TIME和AP.OVERBOOKINGDATE就是打单的时间,一个为Null的话就去另外一个 crtdate就是创建的时间 。创建的时间一定比打单的时间小。比如AP.NOTPERFECT_TIME不为null ,值为2016-11-28 15:30:26操作过 ,创建的时间是2016-11-28 15:15:00 这个时间。
回复 点赞
一丁丁点的爆炸头 2016年11月29日
其中的 CASE
WHEN (SELECT COUNT(*)
FROM HOLIDAY H
WHERE H.HL_DATE =
(TO_DATE(AP.NOTPERFECT_TIME, 'yyyy-mm-dd'))) = 0 holiday中存的是2016年所有的假日包括周末个调休的日期。
回复 点赞
一丁丁点的爆炸头 2016年11月29日
我的意思是当上面的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);
回复 点赞
js14982 2016年11月29日
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
回复 点赞
js14982 2016年11月29日
报什么错? 还有测试数据给点
回复 点赞
发动态
发帖子
基础和管理
创建于2007-09-28

7175

社区成员

9.5w+

社区内容

Oracle 基础和管理
社区公告
暂无公告