MIN date among three columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

MIN date among three columns

Author

Topic

slider
Starting Member

11 Posts

Posted - 2007-03-01 : 15:15:28

I need to update a column based on the values in three columns, all dates.

if all three columns are NULL then update value is also NULL

if any of the three columns have data then i want to update with the earliest date e.g. if columns A, B, C have the following values
2/15/1965,NULL,12/23/1959 then i should get back 12/23/1959 as the value to update with.

Now how can i accomplish that, without having to use cursor etc

Thanks for any help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-01 : 17:58:09

Here is one way:

DECLARE @MyTable TABLE(c1 DATETIME, c2 DATETIME, c3 DATETIME)

INSERT @MyTable
SELECT '2/15/1965', NULL, '12/23/1949' UNION ALL
SELECT '2/15/1971', NULL, '12/23/1959'

SELECT 
	CASE
		WHEN (COALESCE(c1, CAST('9999-12-31' AS DATETIME)) < COALESCE(c2, CAST('9999-12-31' AS DATETIME)) AND COALESCE(c1, CAST('9999-12-31' AS DATETIME)) < COALESCE(c3, CAST('9999-12-31' AS DATETIME))) THEN c1
		WHEN (COALESCE(c2, CAST('9999-12-31' AS DATETIME)) < COALESCE(c1, CAST('9999-12-31' AS DATETIME)) AND COALESCE(c2, CAST('9999-12-31' AS DATETIME)) < COALESCE(c3, CAST('9999-12-31' AS DATETIME))) THEN c2
		WHEN (COALESCE(c3, CAST('9999-12-31' AS DATETIME)) < COALESCE(c1, CAST('9999-12-31' AS DATETIME)) AND COALESCE(c3, CAST('9999-12-31' AS DATETIME)) < COALESCE(c2, CAST('9999-12-31' AS DATETIME))) THEN c3
		ELSE NULL
	END
FROM @MyTable

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-01 : 18:17:57

[code]
select
ID,
[earliest date] = Min(aa.Date)
from
(
select ID, Date = a from Mytable where a is not null union all
select ID, Date = b from Mytable where b is not null union all
select ID, Date = c from Mytable where c is not null
) aa
group by
aa.ID

[/code]

CODO ERGO SUM

f.tromp
Starting Member

1 Post

Posted - 2007-03-08 : 18:38:26

-- A combination of both solutions
-- Selection for each row

DECLARE @MyTable TABLE(c1 DATETIME, c2 DATETIME, c3 DATETIME)

INSERT @MyTable
SELECT CONVERT(DATETIME, '15-2-1965', 105), NULL, CONVERT(DATETIME, '23-12-1949', 105)
UNION ALL
SELECT CONVERT(DATETIME, '15-2-1971', 105), NULL, CONVERT(DATETIME, '23-12-1959', 105)

SELECT
--Select minimum date for each row
(Select min(t1.D) from (
select c1 as D union
select c2 as D union
select c3 as D
) as t1)

from @MyTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources