SELECT 'INITIALIZE' AS PROCESS_DESC,
floor((MAX(EXEC_DATE)-MIN(EXEC_DATE))*24)
|| ' HOURS '
|| mod(floor((MAX(EXEC_DATE)-MIN(EXEC_DATE))*24*60),60)
|| ' MINUTES '
|| mod(floor((MAX(EXEC_DATE)-MIN(EXEC_DATE))*24*60*60),60)
|| ' SECS ' time_difference
FROM spool_table
WHERE process_desc ='INITIALIZE'
AND to_date(EXEC_DATE, 'DD-MON-YYYY') =
(SELECT to_date(EXEC_DATE, 'DD-MON-YYYY')=
FROM spool_table
WHERE process_desc='INITIALIZE'
)
UNION ALL
SELECT 'PRELIM' AS PROCESS_DESC,
floor((MAX(EXEC_DATE)-MIN(EXEC_DATE))*24)
|| ' HOURS '
|| mod(floor((MAX(EXEC_DATE)-MIN(EXEC_DATE))*24*60),60)
|| ' MINUTES '
|| mod(floor((MAX(EXEC_DATE)-MIN(EXEC_DATE))*24*60*60),60)
|| ' SECS ' time_difference
FROM spool_table
WHERE process_desc ='PRELIM'
AND to_date(EXEC_DATE, 'DD-MON-YYYY')=
(SELECT to_date(MAX(EXEC_DATE), 'DD-MON-YYYY')
FROM spool_table
WHERE process_desc='PRELIM'
)
在这种情况下,
您可以使用 FIRST(或 LAST(来查找最新日期的最小日期时间:
with cte (process_desc, max_exec_date, min_exec_date) as (
select
process_desc,
max(exec_date) max_exec_date,
min(exec_date) keep (dense_rank last order by trunc(exec_date) nulls first) min_exec_date
from spool_table s
where process_desc in ('PRELIM', 'INITIALIZE') -- include or exclude what process you want here
group by process_desc
)
select
process_desc,
floor((max_exec_date-min_exec_date)*24) || ' HOURS ' ||
mod(floor((max_exec_date-min_exec_date)*24*60),60) || ' MINUTES ' ||
mod(floor((max_exec_date-min_exec_date)*24*60*60),60)
|| ' SECS ' time_difference
from cte;
CTE 在那里澄清查询。如果需要,您可以将其作为子查询放置,甚至可以不使用这样的子查询:
select
process_desc,
floor((max(exec_date)-min(exec_date) keep (dense_rank last order by trunc(exec_date) nulls first))*24) || ' HOURS ' ||
mod(floor((max(exec_date)-min(exec_date) keep (dense_rank last order by trunc(exec_date) nulls first))*24*60),60) || ' MINUTES ' ||
mod(floor((max(exec_date)-min(exec_date) keep (dense_rank last order by trunc(exec_date) nulls first))*24*60*60),60)
|| ' SECS ' time_difference
from spool_table s
where process_desc in ('PRELIM', 'INITIALIZE')
group by process_desc;
有点幼稚的方式是使用相关的子查询:
select process_desc,
floor((max(exec_date)-min(exec_date))*24)
|| ' HOURS '
|| mod(floor((max(exec_date)-min(exec_date))*24*60),60)
|| ' MINUTES '
|| mod(floor((max(exec_date)-min(exec_date))*24*60*60),60)
|| ' SECS ' time_difference
from spool_table s
where process_desc in ('PRELIM', 'INITIALIZE')
and trunc(exec_date) = (
select trunc(max(exec_date))
from spool_table t
where t.process_desc = s.process_desc
)
group by process_desc;