Re: newbie question with cursors/dates

From: MindSpring User <sallsopp_at_mindspring.com>
Date: Mon, 12 Mar 2001 14:15:02 -0500
Message-ID: <98j77c$g7f$1@slb7.atl.mindspring.net>

Thanks for the reply. I was multiplying by 1440 after subtracting the two dates. The days are the same day, but the times will be different. I want to get the value in minutes - like 4:20 PM - 4:10 PM would equal 10 minutes.

Here is my code:
 (Hold_Red table is simply a table containing start and stop times in DATE datatype, OPNR is a character string representing a launch number, ATTEMPT is a number):

CREATE OR REPLACE FUNCTION CALC_RANGE_RED  ( V_OPNR IN HOLD_RED.OPNR%TYPE,
   V_ATTEMPT IN HOLD_RED.ATTEMPT%TYPE)
 RETURN NUMBER
IS

/* create cursor of all the red range values for this OPNR and ATTEMPT */ CURSOR Hold_Red_Cursor IS

    SELECT OPNR, ATTEMPT, STARTTIME, ENDTIME, TYPE FROM HOLD_RED   WHERE OPNR = V_OPNR AND ATTEMPT = V_ATTEMPT AND TYPE = 'RED'   ORDER BY STARTTIME; /* variables to store the accumulated time and previous record time values */

v_start   DATE;
v_end   DATE;
v_total_time  NUMBER(7,2);

recnum NUMBER;

BEGIN /* initialize count variables */
v_total_time := 0;
recnum := 1;

/* loop through all the records, determine any overlaps and calculate total time */
FOR hold_red_rec IN hold_red_cursor
LOOP
 /* If first record then initialize variables  IF recnum = 1
  THEN
  v_start := hold_red_rec.starttime;
  v_end := hold_red_rec.endtime;
 END IF;  /* first, check for no overlap */
 IF (hold_red_rec.starttime >= endtime)
  THEN

   v_total_time := v_total_time + ((v_end - v_start)*1440);
   v_start := hold_red_rec.startime;
   v_end := hold_red_rec.endtime;

 /* next check for one time overlapping */  ELSIF hold_red_rec.starttime <= v_end AND hold_red_rec.endtime >= v_end   THEN

   v_start := v_end;
   v_end := hold_red_rec.endtime;
   v_total_time := v_total_time + ((v_end - v_start)*1440);

 /* else both new times are within the last time range, so don't add anything */
 /* hold_red_rec.starttime > v_start AND hold_red_rec.end_time < v_end */

 End If;

 /* increment record number */
 recnum := recnum +1;

END LOOP; RETURN v_total_time;

END; "Andrew Velichko" <andrew.velichko_at_globeinteractive.com> wrote in message news:tcQq6.152313$Z2.1922915_at_nnrp1.uunet.ca...
> Hi!
>
> Difference between 2 dates is returned in days.
> Each day contain 1440 minutes, so you need to
> multiply you difference just by this number:
>
> select (sysdate - (sysdate-1/24))*1440 from dual;
> returns
> 60.0000000000000000000000000000000000001
> minutes, which is correct.
>
> Andrew Velichko
> Brainbench MVP for Oracle Developer 2000
> http://www.brainbench.com
> --------------------------------------------------------------
>
>
> "MindSpring User" <sallsopp_at_mindspring.com> wrote in message
> news:98gg45$v9j$1_at_slb5.atl.mindspring.net...
> > Hi,
> > I am very new to ORACLE coming from an Access developers background.
 I
> > am trying to pull two dates from records in a table (called Hold_Red)
 and
> > calculate total time elapsed. If any of the times overlap from any
 previous
> > times, that amount of time must NOT be double counted. I am sure I need
 a
> > cursor to pull out just the records that I need, but how do I set my
 inital
> > values outside of the cursor? The inital variables should be equal to
 the
> > first records values. I think my logic is correct from there, except
 that
 I
> > do not understand how to use the date difference to calculate minutes.
 I
> > thought if I subtracted the two and multiplied by the number of minutes
 in
 a
> > day, I would get the value in minutes, but it always comes up with zero.
> > This may ofcourse be because I do not have my variable initialized
 properly!
> > Please help!
> > Thanks
> > S. Allsopp
> >
> >
> >
>
>
Received on Mon Mar 12 2001 - 13:15:02 CST