Re: SQL or PL/SQL Help of Just Plain Help!! (newbie)

From: <pberetta_at_my-deja.com>
Date: Thu, 20 Jan 2000 11:28:27 GMT
Message-ID: <866rgh$rqh$1_at_nnrp1.deja.com>

Robin,
  If you can be sure the result of (revised date - entered date) will always be less than 32 days, you can use the following:

SELECT db1.ticketno,
       db1.dateentered,
       db1.daterevised,
       TO_CHAR((TO_DATE('01012000000000','DDMMYYYYHH24MISS') +
       ( db1.daterevised - db1.dateentered)),'DD HH24 MI SS') AS "Cycle

Time"
FROM your_table
WHERE conditions...;

Just add whatever WHERE clause is necessary to limit your output to the range of dates you are interested in.
How it works -
Subtracting date entered from date revised yields a number in days and fractional days between the two datetime values. Then we add that number to a fixed point in time - the first day of a 31 day month at midnight. As long as we add less than 32 days, the day, hour, minute, second values from the resulting date will represent the days, hours, minutes and seconds between the two original dates (as long as we return the hours in 24 hour clock format). So the final, outer TO_CHAR does just that.

If you can have periods of 32 days or more, you can revise this to:

SELECT db1.ticketno,
       db1.dateentered,
       db1.daterevised,
       TRUNC( db1.daterevised - db1.dateentered) AS "Days",
       TO_CHAR((TO_DATE('01012000000000','DDMMYYYYHH24MISS') +
       ( db1.daterevised - db1.dateentered)),'HH24 MI SS') AS "HH MM

SS"
FROM your_table
WHERE conditions...;

This version grabs the days directly, so it will even work for dates that are years apart. The example below is run against the SCOTT/TIGER EMP table.

SQL> SELECT empno,

  2         TO_CHAR(SYSDATE,'MM DD YYYY HH24 MI SS'),
  3         TO_CHAR(hiredate,'MM DD YYYY HH24 MI SS'),
  4         TRUNC(SYSDATE - hiredate) AS "Days",
  5         TO_CHAR((TO_DATE('01012000000000','DDMMYYYYHH24MISS') +
  6         (SYSDATE - hiredate)),'HH24 MI SS') AS "HH MM SS"

  7 FROM emp
  8 WHERE empno > 7800;

     EMPNO TO_CHAR(SYSDATE,'MM TO_CHAR(HIREDATE,'M Days HH MM SS

---------- ------------------- ------------------- ---------- --------
      7839 01 20 2000 06 20 28 11 17 1981 00 00 00       6638 06 20 28
      7844 01 20 2000 06 20 28 09 08 1981 00 00 00       6708 06 20 28
      7876 01 20 2000 06 20 28 01 12 1983 00 00 00       6217 06 20 28
      7900 01 20 2000 06 20 28 12 03 1981 00 00 00       6622 06 20 28
      7902 01 20 2000 06 20 28 12 03 1981 00 00 00       6622 06 20 28
      7934 01 20 2000 06 20 28 01 23 1982 00 00 00       6571 06 20 28

6 rows selected.

If you are returning a very large number of rows, and the interval can never be 32 days or more; then use the first version, it will be slightly faster. Otherwise, use the second version. Hope this helps,
Paul

In article <B4ABE5F2.A7D%rwboscia_at_worldnet.att.net>,   Boscia <rwboscia_at_worldnet.att.net> wrote:
> I am running Oracle Reports and attempting to write SQL to pull data
from
[Quoted] > tables...
>
> I have fields of data that are DATE (mm-dd-yyy hh:mm).
>
> Example: db1.ticketno db1.dateentered db1.daterevised
> 32 01-02-2000 00:00 01-05-2000
11:59
>
> How can I subtract one date from another and come up with a cycle
time
> report that would show the amount of time in days, hours and minutes
that it
> took from the time that ticket was entered until it was revised?
>
> (If PL/SQL is needed, please go easy on me, since I am not very
familiar
> with it and no one in my office knows it either!!)
> [Quoted]
> Thanks in advance...
> Robin
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 20 2000 - 12:28:27 CET