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

CREATE 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




Show quoteHide quote

Amit wrote:
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

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"
<A***@discussions.microsoft.com> wrote:

Show quoteHide quote

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 ;

use trigger
Is subquery bad?
Generating the perfect Primary Key: Using CRC32? Using MD5?
block comment???
Increased Datetime Precision