Home » SQL & PL/SQL » SQL & PL/SQL » add value to one record based on another record
add value to one record based on another record [message #183963] Mon, 24 July 2006 09:18 Go to next message
imready
Messages: 1
Registered: July 2006
Location: Columbus, Oh
Junior Member
Hi,
Thanks for the reply,

I have a table that has 3 fields

Field A Field B Field C
101 Apple 3/2/06 3:00 AM
101 Orange 3/2/06 4:00 AM
101 Banana 3/2/06 9:00 AM
102 Orange 3/2/06 4:00 AM
102 Grapes 3/2/06 5:00 AM
102 Orange 3/2/06 10:00 AM
103 Orange 3/2/06 4:00 AM
103 Grapes 3/2/06 5:00 AM

I already have this data for several last months and i need to generate on demand report, to show the number of times one record gets updated (between two dates) and what was the previous value for that record before its got updated.
so i was thinking of creating a table where i can upload all the data between two dates(for which report needs to be created) and use some query to add another column that can pull previous value of Field B for same previous Value of field A.

EXAMPLE : now the need to create another field BB that has previous value of Field B if Value of Field A = previous value(Field A) at previous time.

e.g

Field A Field BB Field B Field C
101 ------ Apple 3/2/06 3:00 AM
101 Apple Orange 3/2/06 4:00 AM
101 Orange Banana 3/2/06 9:00 AM
102 ------ Orange 3/2/06 4:00 AM
102 Orange Grapes 3/2/06 5:00 AM
102 Grapes Orange 3/2/06 10:00
103 ------ Orange 3/2/06 4:00 AM
103 Orange Grapes 3/2/06 5:00 AM


please suggest me if anybdy knows

Thanks In Advance



Re: add value to one record based on another record [message #184035 is a reply to message #183963] Mon, 24 July 2006 23:40 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

SQL> select  * from tab_field;

   FIELD_A FIELD_B    FIELD_C
---------- ---------- -----------------------
       101 Apple      02-MAR-2006 03:00:00 AM
       101 Orange     02-MAR-2006 04:00:00 AM
       101 Banana     02-MAR-2006 09:00:00 AM
       102 Orange     02-MAR-2006 04:00:00 AM
       102 Grapes     02-MAR-2006 05:00:00 AM
       102 Orange     02-MAR-2006 10:00:00 AM
       103 Orange     02-MAR-2006 04:00:00 AM
       103 Grapes     02-MAR-2006 05:00:00 AM

8 rows selected.

SQL> SELECT FIELD_A,
  2        lag(FIELD_B) OVER (PARTITION BY FIELD_A ORDER BY ROWNUM)FILD_BB,
  3         FIELD_B,
  4         FIELD_C
  5  FROM  tab_field;

   FIELD_A FILD_BB    FIELD_B    FIELD_C
---------- ---------- ---------- -----------------------
       101            Apple      02-MAR-2006 03:00:00 AM
       101 Apple      Orange     02-MAR-2006 04:00:00 AM
       101 Orange     Banana     02-MAR-2006 09:00:00 AM
       102            Orange     02-MAR-2006 04:00:00 AM
       102 Orange     Grapes     02-MAR-2006 05:00:00 AM
       102 Grapes     Orange     02-MAR-2006 10:00:00 AM
       103            Orange     02-MAR-2006 04:00:00 AM
       103 Orange     Grapes     02-MAR-2006 05:00:00 AM

8 rows selected.

SQL>


Thumbs Up
Rajuvan.

[Updated on: Mon, 24 July 2006 23:43]

Report message to a moderator

Previous Topic: Decode with multiple values
Next Topic: Bulk Insert error - URGENT
Goto Forum:
  


Current Time: Thu Dec 08 12:52:48 CST 2016

Total time taken to generate the page: 0.12237 seconds