Home » SQL & PL/SQL » SQL & PL/SQL » compare (oracle9i)
compare [message #432506] Tue, 24 November 2009 10:13 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Hi experts
How can we subtract last value of one group from the first value of another group.

The data is like this
CYCLE_NO CYCLE_DATE
Cycle1 10-MAR-09
Cycle1 15-MAR-09
Cycle1 17-MAR-09
Cycle2 25-MAR-09
Cycle2 01-MAR-09
Cycle2 12-APR-09
Cycle2 15-APR-09
Cycle3 09-MAY-09
Cycle3 14-MAY-09

We should subtract like this (17-MAR-09)-(25-MAR-09)
(15-APR-09)-(09-MAY-09)
Please help me
Re: compare [message #432511 is a reply to message #432506] Tue, 24 November 2009 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search min and max for each group then use LAG to get the difference (this is an example, there are many other ways).

Michel Cadot wrote on Tue, 24 November 2009 17:39

Also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Use SQL*Plus and copy and paste what you already tried.

Regards
Michel

[Updated on: Tue, 24 November 2009 10:41]

Report message to a moderator

Re: compare [message #432515 is a reply to message #432506] Tue, 24 November 2009 11:02 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SQL> select to_date('17-MAR-09','DD-MON-YY')-to_date('25-MAR-09','DD-MON-YY') FROM DUAL;

TO_DATE('17-MAR-09','DD-MON-YY')-TO_DATE('25-MAR-09','DD-MON-YY')
-----------------------------------------------------------------
							       -8


Why do you want negative results?
Previous Topic: Coverting time to number and subtracting the two
Next Topic: Please hepl me Urgent
Goto Forum:
  


Current Time: Sat Dec 03 08:16:49 CST 2016

Total time taken to generate the page: 0.21157 seconds