tim_cs
asked on
Query help needed
I was wondering if there is any way to pull two times from an oracle database and get the total time between the two? I have the following query and just about everything is near working as far as I know except for the time difference. You can see in my query what I have randomly tried.
select
a.display, A.hire_dt, b.Used, c.schedule, D.discr_time
from
problogs_team A,
( select
team, count(cal_id) AS used
from
problogs_cal
where
cal_type = 'V' AND
START_DT > '1-jan-2004' AND
START_DT < '25-mar-2004'
GROUP BY team
) B,
( select
team, count(cal_id) as schedule
from
problogs_cal
where
cal_type = 'V' and
start_dt > '25-mar-2004' and
start_dt < '1-jan-2005'
GROUP BY TEAM
) C,
( select
team, SUM(TO_DATE(START_DT, 'HH24:MI:SS ') + TO_DATE(END_DT, 'HH24:MI:SS ')) AS discr_time
from
problogs_cal
where
cal_type = 'D' AND
START_DT > '1-jan-2004' AND
START_DT < '25-mar-2004'
GROUP BY team
) D
where
A.TEAM = B.TEAM (+) AND
A.TEAM = C.TEAM (+) AND
A.TEAM = D.TEAM (+) AND
A.MGR_ID = 'ZZZZZZZ;' AND
A.PASS IS NOT NULL
select
a.display, A.hire_dt, b.Used, c.schedule, D.discr_time
from
problogs_team A,
( select
team, count(cal_id) AS used
from
problogs_cal
where
cal_type = 'V' AND
START_DT > '1-jan-2004' AND
START_DT < '25-mar-2004'
GROUP BY team
) B,
( select
team, count(cal_id) as schedule
from
problogs_cal
where
cal_type = 'V' and
start_dt > '25-mar-2004' and
start_dt < '1-jan-2005'
GROUP BY TEAM
) C,
( select
team, SUM(TO_DATE(START_DT, 'HH24:MI:SS ') + TO_DATE(END_DT, 'HH24:MI:SS ')) AS discr_time
from
problogs_cal
where
cal_type = 'D' AND
START_DT > '1-jan-2004' AND
START_DT < '25-mar-2004'
GROUP BY team
) D
where
A.TEAM = B.TEAM (+) AND
A.TEAM = C.TEAM (+) AND
A.TEAM = D.TEAM (+) AND
A.MGR_ID = 'ZZZZZZZ;' AND
A.PASS IS NOT NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select (date2 - date1)*24*60*60 from table;
24 hours x 60 minutes x 60 secs
2. To convert to hours:
select (date2 - date1)*24 from table;
HTH