add value to one record based on another record

[pkohli8]

Hi,
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

 

[SantaMufasa]

Pkohli8,

In your earlier thread, Jimbo was concerned that your inquiries relate to class assignments, with which we are not allowed to help. Your sample data do look suspiciously like classroom assignments.

Before we offer specific suggestions, is there some way that you can establish that your questions relate to work and not to classroom activities?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pkohli8]

Hi Santa,
The example that i have given was simliar to the work assignment , i thgt it would be easier to understand problem with simple example.
If you want to know the exact sample data from my work problem i can provide that...

here is the table,

I have table that includes following fields

SS_ID Asset_Center Time

as similiar to explained in previous post (Field A, Field B. Field C)

Customer can update the value for Asset_Center related to SS_ID and TIme field shows the time when the change has been made.

So now i have to create table which also includes Previous_Asset_Center for the Existing data. means i also want to show wat was previous Asset_center(If Any) Along with Asset_Center.

I hope i am clear this time.

Thanks in Advance

 

[pkohli8]

here is the sample data

SS_ID Asset_Center Time
271620 CENTRAL TECHNOLOGY 7/23/2006 4:12:32 AM
271620 CENTRAL TECHNOLOGY 7/23/2006 4:12:33 AM
50805 ASSET 7/23/2006 3:29:37 AM
50805 TREASURY AND SECURITIES 7/23/2006 3:29:38 AM
50805 CENTRAL TECHNOLOGY AND 7/23/2006 3:29:40 AM
50805 RETAIL FINANCIAL SERV 7/23/2006 3:29:42 AM

 

[SantaMufasa]

Here is a solution using your original data (since your original data have verifiable results):

select a.fielda
      ,nvl((select fieldb
          from pko2
         where fieldc = (select max(fieldc) from pko2
                          where fieldc < a.fieldc
                            and a.fielda = fielda)
           and a.fielda = fielda),'------') fieldbb
      ,a.fieldb
      ,to_char(a.fieldc,'mm/dd/yy hh:mi AM')fieldc
from pko2 a
order by a.fielda, a.fieldc;

FIELDA FIELDBB    FIELDB     FIELDC
------ ---------- ---------- -----------------
   101 ------     Apple      03/02/06 03:00 AM
   101 Apple      Orange     03/02/06 04:00 AM
   101 Orange     Banana     03/02/06 09:00 AM
   102 ------     Orange     03/02/06 04:00 AM
   102 Orange     Grapes     03/02/06 05:00 AM
   102 Grapes     Orange     03/02/06 10:00 AM
   103 ------     Orange     03/02/06 04:00 AM
   103 Orange     Grapes     03/02/06 05:00 AM

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pkohli8]

Hi Musfasa,
Thanks Very Much, Its Perfect

...