 calculating difference of alternate rows thru forms6i. [message #109927] |
Tue, 01 March 2005 23:30  |
Nilesh Kunte
Messages: 30
Registered: December 2004
Location: Indore
|
Member |
|
|
Hi,
I have a column in which iam storing near about 10,000 records in date format(including time)like this "03-mar-04 09:02:34." Now i want to get the difference of alternate rows i.e. row2-row1, row4-row3, etc. in another field say diff_tm.
how should i proceed through forms6i. is there any direct function in forms6i or i have to write a program unit and call the difference field in the form. then what should be that program unit (return type as well as code).
|
|
|
|
| Re: calculating difference of alternate rows thru forms6i. [message #109939 is a reply to message #109927] |
Wed, 02 March 2005 02:20  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Using Oracle's analyitc functions, you do it like this:SQL> SELECT ename current_name
2 , hiredate current_hd
3 , LAG(hiredate) OVER ( ORDER BY hiredate ASC ) prev_hd
4 , hiredate - LAG(hiredate) OVER ( ORDER BY hiredate ASC ) diff
5 FROM emp
6 ORDER BY hiredate ASC
7 /
CURRENT_NA CURRENT_HD PREV_HD DIFF
---------- ----------- ----------- ---------
SMITH 17-DEC-1980
ALLEN 20-FEB-1981 17-DEC-1980 65
WARD 22-FEB-1981 20-FEB-1981 2
JONES 02-APR-1981 22-FEB-1981 39
BLAKE 01-MAY-1981 02-APR-1981 29
CLARK 09-JUN-1981 01-MAY-1981 39
TURNER 08-SEP-1981 09-JUN-1981 91
MARTIN 28-SEP-1981 08-SEP-1981 20
KING 17-NOV-1981 28-SEP-1981 50
JAMES 03-DEC-1981 17-NOV-1981 16
FORD 03-DEC-1981 03-DEC-1981 0
MILLER 23-JAN-1982 03-DEC-1981 51
SCOTT 09-DEC-1982 23-JAN-1982 320
ADAMS 12-JAN-1983 09-DEC-1982 34
14 rows selected.
Perhaps this article on the FAQ is worth to look at if you're interested in analytic functions.
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
