Max date in a row

Hi,
I would like to know how to find the max of 4 dates in one row.
so if we have ssn, date1, date2, date3, date4
456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
my output should give me
456123789, 1/2/2006
ThanksCREATE TABLE #foo
(
ssn CHAR(9),
d1 SMALLDATETIME,
d2 SMALLDATETIME,
d3 SMALLDATETIME,
d4 SMALLDATETIME
);
SET NOCOUNT ON;
INSERT #foo SELECT '111111111', '20050101', '20050505', '20050603',
'20050401';
INSERT #foo SELECT '222222222', '20050601', '20050505', '20050203',
'20050201';
INSERT #foo SELECT '333333333', '20050601', '20050601', '20050203',
'20050201';
INSERT #foo SELECT '333333333', '20050601', '20050602', '20050603',
'20050604';
SELECT ssn, d = MAX(d)
FROM
(
SELECT ssn, d = d1 FROM #foo
UNION ALL SELECT ssn, d = d2 FROM #foo
UNION ALL SELECT ssn, d = d3 FROM #foo
UNION ALL SELECT ssn, d = d4 FROM #foo
) x
GROUP BY ssn;
DROP TABLE #foo;
Can I recommend this structure instead:
CREATE TABLE dbo.People
(
ssn CHAR(9) PRIMARY KEY
);
CREATE TABLE dbo.PeopleDates
(
ssn CHAR(9) NOT NULL FOREIGN KEY REFERENCES dbo.People(ssn),
dateInstance TINYINT NOT NULL, -- check for 1-4?
dateValue SMALLDATETIME
);
INSERT dbo.People
SELECT '111111111'
UNION ALL SELECT '222222222'
UNION ALL SELECT '333333333';
INSERT dbo.PeopleDates
SELECT '111111111', 1, '20050101'
UNION ALL SELECT '111111111', 2, '20050505';
/* ...... */
More work up front, and slightly larger storage cost (though you could
offset that a bit by using an INT for the key), but it is more relational in
nature, and look how easy it makes your queries:
SELECT ssn, MAX(dateValue)
FROM dbo.PeopleDates
GROUP BY ssn;
And as well as making this type of query much simpler, you don't have to go
change things when you add a 5th date. (In your current model, you need to
change the schema *and* change the query.)
In addition, I encourage not thinking about dates in these string formats,
or at least when you are explaining an issue to other people, to avoid
confusion and ambiguity. Are your dates:
(a) Mar 12 2005, May 12 2005, Aug 11 2005, Feb 1 2006
or
(b) Dec 3 2005, Dec 5 2005, Nov 8 2005, Jan 2 2005
?
In this case, it was easy to pick out the latest date you expected in the
result, because it was the only one in 2006. But if you included 2/1/2006
as well, I'd be at a loss without requesting further clarification.
You should strive to use string representations of dates that are 100%
unambiguous to both people and code. For example, 'YYYYMMDD' will always
work, no matter who you're talking to or what your SQL Server's regional
settings, dateformat, language, etc.
A
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:8E25E6E0-E4E0-4739-908A-B995314FFFF6@.microsoft.com...
> Hi,
> I would like to know how to find the max of 4 dates in one row.
> so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
> my output should give me
> 456123789, 1/2/2006
> Thanks|||Amit wrote:
> Hi,
> I would like to know how to find the max of 4 dates in one row.
> so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
> my output should give me
> 456123789, 1/2/2006
> Thanks
Table design aside, you can use a scalar function here:
Select
ssn,
dbo.fnGetMaxDate(date1, date2, date3, date4)
From
dbo.MyTable
Create Function dbo.fnGetMaxDate (
@.date1 datetime, @.date2 datetime, @.date3 datetime, @.date4 datetime )
Returns datetime
as
Begin
declare @.datefinal datetime
set @.datefinal = @.date1
If @.date2 > @.datefinal
set @.datefinal = @.date2
If @.date3 > @.datefinal
set @.datefinal = @.date3
If @.date4 > @.datefinal
set @.datefinal = @.date4
Return @.datefinal
End
David Gugick - SQL Server MVP
Quest Software|||SELECT SSN,
CASE WHEN date1 > date2
AND date1 > date3
AND date1 > date4
THEN date1
WHEN date2 > date3
AND date2 > date4
THEN date2
WHEN date3 > date4
THEN date3
ELSE date4
END as MaxDate
FROM SomeTable
Roy Harvey
Beacon Falls, CT
On Thu, 2 Mar 2006 11:35:02 -0800, "Amit"
<Amit@.discussions.microsoft.com> wrote:

>Hi,
>I would like to know how to find the max of 4 dates in one row.
>so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
>my output should give me
>456123789, 1/2/2006
>Thanks|||Another approach:
SELECT ssn,
MAX( CASE n WHEN 1 THEN dt1
WHEN 2 THEN dt2
WHEN 3 THEN dt3
WHEN 4 THEN dt4
END )
FROM tbl, ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) N ( n )
GROUP BY ssn ;
Anith