17,086
社区成员
发帖
与我相关
我的任务
分享
SELECT
nvl(round(avg(AVG_DEAL_TIME), 1), 0) as SHEET_AVEPROTIME,
nvl(round(max(MAX_DEAL_TIME), 1), 0) as SHEET_MAXPROTIME,
nvl(round(min(MIN_DEAL_TIME), 1), 0) as SHEET_MINPROTIME
FROM (SELECT
T.SEND_FLAG,
T.DEAL_FLAG,
ROUND(AVG(T.OVER_DATE - T.CREATE_DATE) * 24 * 3600, 1) AVG_DEAL_TIME,
ROUND(MAX(T.OVER_DATE - T.CREATE_DATE) * 24 * 3600, 1) MAX_DEAL_TIME,
ROUND(MIN(T.OVER_DATE - T.CREATE_DATE) * 24 * 3600, 1) MIN_DEAL_TIME
FROM (SELECT *
FROM PG_J_SHEET
WHERE CREATE_DATE BETWEEN
TO_DATE('2010-08-01 00:00:00', 'YYYY-MM-DD HH24:Mi:SS') AND
TO_DATE('2010-08-07 23:59:59', 'YYYY-MM-DD HH24:Mi:SS')) T
GROUP BY T.SEND_FLAG,
T.DEAL_FLAG) G
SELECT
nvl(round(avg(AVG_DEAL_TIME), 1), 0) as SHEET_AVEPROTIME,
nvl(round(max(MAX_DEAL_TIME), 1), 0) as SHEET_MAXPROTIME,
nvl(round(min(MIN_DEAL_TIME), 1), 0) as SHEET_MINPROTIME
FROM (SELECT
T.SEND_FLAG,
T.DEAL_FLAG,
case when T.DEAL_FLAG<>9 then ROUND(AVG(T.OVER_DATE - T.CREATE_DATE) * 24 * 3600, 1) else 0 end AVG_DEAL_TIME,
case when T.DEAL_FLAG<>9 then ROUND(MAX(T.OVER_DATE - T.CREATE_DATE) * 24 * 3600, 1) else 0 end MAX_DEAL_TIME,
case when T.DEAL_FLAG<>9 then ROUND(MIN(T.OVER_DATE - T.CREATE_DATE) * 24 * 3600, 1) else 0 end MIN_DEAL_TIME
FROM (SELECT *
FROM PG_J_SHEET
WHERE CREATE_DATE BETWEEN
TO_DATE('2010-08-01 00:00:00', 'YYYY-MM-DD HH24:Mi:SS') AND
TO_DATE('2010-08-07 23:59:59', 'YYYY-MM-DD HH24:Mi:SS')) T
GROUP BY T.SEND_FLAG,
T.DEAL_FLAG) G