|
Home > Archive > MS SQL Server > April 2006 > Max [date] from DIFFERENT columns?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Max [date] from DIFFERENT columns?
|
|
| Alec MacLean 2006-04-06, 9:23 am |
| 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
| |
| Edgardo Valdez, MCSD, MCDBA 2006-04-06, 8:23 pm |
| 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
>
>
>
>
|
|
|
|
|