How to calculate the difference between dates on different rows [message #441] |
Tue, 12 February 2002 13:48  |
UmaSund
Messages: 22 Registered: February 2002
|
Junior Member |
|
|
Hi
I have a set of rows returned by a query regarding a Job. One of the Columns is last_update date.The result is ordered by this date asc only.
Now, I need to Calculate the difference between row 2 date and row 1 date and display it for row 1, then difference bewteen row 3 and row 2 date and display it for row 2 and so on.
The numbers of rows which will be returned for a Job will vary. Some may return 3 others may return say 20.
Is this calculation possible using sql/ pl/sql?
How can it be achieved.
Thanks in adavance.
Thanks
-UmaSund
|
|
|
|
Re: How to calculate the difference between dates on different rows [message #459 is a reply to message #441] |
Wed, 13 February 2002 13:20  |
Mike
Messages: 417 Registered: September 1998
|
Senior Member |
|
|
Hi,
Have a look at the lag/lead function:
{LAG | LEAD}
(<value expression1>, [[<offset> [[, <default>]]]]) OVER
([[PARTITION BY <value expression2>[[,...]]]]
ORDER BY <value expression3> [[collate clause>]]
[[ASC | DESC]] [[NULLS FIRST | NULLS LAST]] [[,...]])
SELECT t_timekey, s_amount,
LAG(s_amount,1) OVER (ORDER BY t_timekey) AS LAG_amount,
LEAD(s_amount,1) OVER (ORDER BY t_timekey) AS LEAD_amount
FROM sales, time
WHERE sales.s_timekey = time.t_timekey
ORDER BY t_timekey;
gives:
T_TIMEKEY......S_AMOUNT............LAG_AMOUNT..........LEAD_AMOUNT
---------......--------............----------..........-----------
99-10-11......................1........................NULL..............................2
99-10-12......................2..............................1..............................3
99-10-13......................3..............................2..............................4
99-10-14......................4..............................4..............................5
99-10-15......................5..............................2........................NULL
This should solve your problem. For more details see:
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76994/analysis.htm#15913
HTH
Mike
|
|
|