Max [date] from DIFFERENT columns?

Hi,

I have a table in which there are several date columns recording different
event points occuring to a record (e.g., date opened, date action-1, etc).

I need to find the most recent date (MAX(date-n)) from across all these
columns to compare with a final closure date.

Is there a (simple/sensible) mechanism that makes this possible?

I have considered using IF/ELSE to try and determine if one is later than
the other, but this seems like a no-go (too complex to implement sensibly).

CASE statement instead maybe?

Any pointers gratefully received...Thx

Al

Alec,

If you do not have a lot of columns, you can use the following approach:

Please let me kow if it helps...

-- BEGIN SCRIPT

declare @table table
(RecordID int
, Created datetime
, Opened datetime
, Updated datetime
)

insert into @table
values (1, getdate(), getdate()+ .10, getdate()+.15)
insert into @table
values (2, getdate()+.3, getdate()+ .40, getdate()+.45)

-- Preview of the table
select * from @table

-- Actual query
select RecordID
, MAX(ActionDate) LatestActionDate
from	(
select RecordId
, Created ActionDate
, 'Created' Action
from @table
union
select RecordId
, Opened ActionDate
, 'Opened' Action
from @table
union
select RecordId
, Updated ActionDate
, 'Updated' Action
from @table
) t1
group by RecordID

-- END SCRIPT
"Alec MacLean" wrote:

> Hi,
>
> I have a table in which there are several date columns recording different
> event points occuring to a record (e.g., date opened, date action-1, etc).
>
> I need to find the most recent date (MAX(date-n)) from across all these
> columns to compare with a final closure date.
>
> Is there a (simple/sensible) mechanism that makes this possible?
>
> I have considered using IF/ELSE to try and determine if one is later than
> the other, but this seems like a no-go (too complex to implement sensibly)
.
>
> CASE statement instead maybe?
>
> Any pointers gratefully received...Thx
>
> Al
>
>
>
>