max date less than existing

[timscronin]

I have a table called ppd2002, which has an id field a testdate field (datetime), and a previoustesdate field (datetime). The testdate field is filled in. I have another table called ppd which has an id field and a testdate field. I need to update the ppd2002 field called previoustestdate with the max(testdate) field from ppd, but that max date must be less than the current testdate in the ppd2002 field. The id field is the link field between the 2 tables. Tried to create a view, then a update but still stuck.

 

[checkai]

give this a shot....not positive if it will work in this instance...

update b
set b.previoustestdate = a.testdate
from ppd a join ppd2002 b on a.id = b.id
where a.testdate < b.testdate

DLC

 

[timscronin]

problem is I need the max last testdate , there may be several records in the ppd table

 

[checkai]

I see what you mean...I would think a view would work then...

create view yaddayadda
as

select id, max(testdate) from ppd group by id

then

update b
set b.previoustestdate = a.testdate
from yaddayadda a join ppd2002 b on a.id = b.id
where a.testdate < b.testdate

yes? no? not positive...

dlc

 

[timscronin]

The problem is the table ppd2002 was created by data from the ppd table, which means the current record in there is already the max date, I need the next max date

 

[swampBoogie]

update ppd2002 
   set previoustestDate = 
    ( select max(testDate)
        from ppd
       where id = ppd2002.id )
  where id in (select id from ppd)
    and testDate > 
  ( select max(testDate)
        from ppd
       where id = ppd2002.id )

 

[rajeevnandanmishra]

Hi Tim, swampboggie

I am not sure about the above query's where clause. It looks to me something complex.
What about the following query (if i have not understood the problem correctly)
-----------------------------------------------
update ppd2002
set previoustestdate = (select max(a.testdate)
from ppd a where a.id = ppd2002.id
and a.testdate <ppd2002.testdate)
-----------------------------------------------



---
Raj