sql server programming: finding the Max date from several fields in the same row? -- sql server programming

Re: finding the Max date from several fields in the same row?

Steve Kass
11/19/2004 5:14:49 PM

Normalize the table, either permanently, or as a derived table or view:

select
id,
max(d) as maxdate
from (
select
id,
case i
when 0 then date0
when 1 then date1
when 2 then date2
when 3 then date3
when 4 then date4
end as d
from tableA cross join (
select 0 as i union all select 1 union all select 2
union all select 3 union all select 4
) T
) T
group by id

Steve Kass
Drew University

[quoted text, click to view]

finding the Max date from several fields in the same row?

Cold Canuck
11/19/2004 10:03:15 PM

Howdy All!

I have tableA:

id integer
date0 datetime
date1 datetime
date2 datetime
date3 datetime
date4 datetime

with values:

1, '11/1/04 12:56.000', null, null, null
23, '11/2/04 12:56.000', '11/2/04 15:56.000', null, null
45, '11/3/04 10:36.000', '11/2/04 12:56.000', '11/4/04 12:06.000', null
322, '11/12/04 12:56.000', '11/13/04 12:56.000', '11/14/04 12:56.000',
'11/15/04 12:56.000'

I'm trying to write some SQL to return the max date for each row:

1, '11/1/04 12:56.000'
23, '11/2/04 15:56.000'
45, '11/4/04 12:06.000'
322, '11/15/04 12:56.000'

Any ideas? I'm stumped.

Thanks,
CC

env: SQL2000 (SP3), W2k3 server

Re: finding the Max date from several fields in the same row?

David Portas
11/19/2004 10:14:40 PM

SELECT id,
(SELECT MAX(dt)
FROM
(SELECT date0 AS dt UNION ALL
SELECT date1 UNION ALL
SELECT date2 UNION ALL
SELECT date3 UNION ALL
SELECT date4) AS X)
AS maxdate
FROM TableA

Are you sure your table is correctly normalized? Do all those dates really
represent *different* attributes in your data? If so, exactly what do you
mean by trying to compare them?

--
David Portas
SQL Server MVP
--

Re: finding the Max date from several fields in the same row?

Cold Canuck
11/19/2004 10:19:37 PM

[quoted text, click to view]

I found it - SELECT id, COALESCE(date4,date3, date2, date1, date0) FROM
tableA

(I forgot about COALESCE, I'm coming from a Sybase background)

cheers,
CC

Re: finding the Max date from several fields in the same row?

David Portas
11/19/2004 10:20:25 PM

COALESCE won't give you the latest date, only the first non-NULL date in the
order you list them.

--
David Portas
SQL Server MVP
--

Re: finding the Max date from several fields in the same row?

Cold Canuck
11/19/2004 10:22:01 PM

[quoted text, click to view]

Thanks David!

I've resolved my problem (see my last post). Unfortunately, I've inherited
this table from a CRM appplication...

CC

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.