Home » SQL & PL/SQL » SQL & PL/SQL » How to calculate the difference between dates on different rows
How to calculate the difference between dates on different rows [message #441] Tue, 12 February 2002 13:48 Go to next message
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 #445 is a reply to message #441] Wed, 13 February 2002 00:51 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
you have to write pl/sql
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 Go to previous message
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
Previous Topic: How can you keep your SQL Session active?
Next Topic: help with PLS-00357 error:
Goto Forum:
  


Current Time: Thu Mar 28 18:23:18 CDT 2024