datediff in Oracle
WITH T as (
SELECT
starttime,
endtime,
TO_TIMESTAMP(
TO_CHAR(endtime, 'dd-mon-yyyy hh24:mi:ss'),
'dd-mon-yyyy hh24:mi:ss') - starttime as d
FROM xx
WHERE create_date > to_date(12-Dec-2012', 'dd-mon-yyyy')
ORDER BY create_date DESC
)
SELECT
starttime as "Start Time",
endtime as "End Time",
EXTRACT (DAY FROM d)*24*60*60+
EXTRACT (HOUR FROM d)*60*60+
EXTRACT (MINUTE FROM d)*60+
EXTRACT (SECOND FROM d) as "Duration in Second",
(EXTRACT (DAY FROM d)*24*60*60+
EXTRACT (HOUR FROM d)*60*60+
EXTRACT (MINUTE FROM d)*60+
EXTRACT (SECOND FROM d)
)/60 as "Duration in Minute"
FROM T;
WITH TT AS (
select 'A' as emp, '|' as lft, '|2' as rgt from dual
union
select 'B' as emp, '2' as lft, '3' as rgt from dual
union
select 'C' as emp, '4' as lft, '||' as rgt from dual
union
select 'D' as emp, '5' as lft, '6' as rgt from dual
union
select 'E' as emp, '7' as lft, '8' as rgt from dual
union
select 'F' as emp, '9' as lft, '|0' as rgt from dual
)
SELECT P2.* FROM TT P2, TT P1 WHERE P1.lft BETWEEN P2.lft AND P2.rgt AND P1.emp = 'C';