aggregating multiple columns into 1

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)

aggregating multiple columns into 1

Author

Topic

mgn0528
Starting Member

7 Posts

Posted - 2004-05-04 : 17:06:37

Ok, I've searched the forums and found some things that seemed to be sort of like what I need, but not.

I need to write a select statement in which I pull the max value from 6 columns into a single column... here's the kicker... for EACH row. NOT the max value in a single column, but the maximum value amoung 6 columns for the current row.

let's say my table looks like this:

table1
cola, colb, date1, date2, date3, date4, date5, date6, colc

...and our data:
'blah', 'blah', '04/05/04', '04/06/04', '04/07/04', '04/08/04', '04/18/04', '04/20/04'

'blam', 'blam', '05/05/04', '05/06/04', '10/31/04', '06/28/04', '10/18/04', '10/19/04'

I need to write the following query (I know this is not correct syntax and key words but I can't think of another way to express the idea...)

SELECT cola, colb, MAX(date1, date2, date3, date4, date5, date6) AS MaxDate, colc FROM table1

this would be the results
cola colb MaxDate
------ ------ ----------
'blah' 'blah' '04/20/04'
'blam' 'blam' '10/31/04'

any deas? Thanks!

ajarlson
Starting Member

8 Posts

Posted - 2004-05-04 : 17:28:41

Off the top of my head I think a big case statement would do the trick:

select
cola,
colb,
case when date1 > date2 and date1 > date3 and date1 > date4 and date1 > date5 and date1 > date6
then date1
when date2 > date1 and date2 > date3 and date2 > date4 and date2 > date5 and date2 > date6
then date2
when date3 > date1 and date3 > date2 and date3 > date4 and date3 > date5 and date3 > date6
then date3
when date4 > date1 and date4 > date2 and date4 > date3 and date4 > date5 and date4 > date6
then date4
when date5 > date1 and date5 > date2 and date5 > date3 and date5 > date4 and date5 > date6
then date5
when date6 > date1 and date6 > date2 and date6 > date3 and date6 > date4 and date6 > date5
then date6
else null END biggest_date
from
table1

but I could probably come up with something prettier with more time.
:^)

ajarlson
Starting Member

8 Posts

Posted - 2004-05-04 : 18:55:28

ps use >= instead of just > to take care of any instances where the dates are the same.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-05 : 08:40:57

Here is a link to a similar discussion. Note the idea to change table structure.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34724

mgn0528
Starting Member

7 Posts

Posted - 2004-05-05 : 09:51:46

Thanks for the replies. I'll give'em a try and let you know. Thanks!

mgn0528
Starting Member

7 Posts

Posted - 2004-05-05 : 10:16:42

Ok, Using your suggestions I've come up with the follwing user-defined function that will serve my needs. After looking at the requirements a bit closer I discovered a pattern or rule for the data.
date1 will always be less than or equal to date2; date2 will always be less than or equal to date3; etc. Dates not in use will be NULL. So, I feed the six date fields to the function and ask it to return the first date that is not NULL starting with date6 and working backwards. Here's the function:

CREATE FUNCTION dbo.Max_Date (@date1 datetime, @date2 datetime, @date3 as datetime, @date4 datetime, @date5 as datetime, @date6 datetime)
RETURNS datetime
AS
BEGIN
DECLARE @MaxDate datetime

IF @date6 IS NOT NULL
SELECT @MaxDate = @date6
ELSE
IF @date5 IS NOT NULL
SELECT @MaxDate = @date5
ELSE
IF @date4 IS NOT NULL
SELECT @MaxDate = @date4
ELSE
IF @date3 IS NOT NULL
SELECT @MaxDate = @date3
ELSE
IF @date2 IS NOT NULL
SELECT @MaxDate = @date2
ELSE
IF @date1 IS NOT NULL
SELECT @MaxDate = @date1
RETURN @MaxDate
END

It's ugly... but, it works! If it were up to me I would better normalized this table so I wouldn't have to do this, but that's out of my hands. Thanks for the direction!

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-05 : 11:10:28

quote:
Originally posted by mgn0528

Ok, Using your suggestions I've come up with the follwing user-defined function that will serve my needs. After looking at the requirements a bit closer I discovered a pattern or rule for the data.
date1 will always be less than or equal to date2; date2 will always be less than or equal to date3; etc. Dates not in use will be NULL. So, I feed the six date fields to the function and ask it to return the first date that is not NULL starting with date6 and working backwards. Here's the function:

If your rule is true MS has already created this function.

It is called COALESCE() and it returns the first non-NULL field in a list of fields COALESCE(date6, date5, date4,...)

mgn0528
Starting Member

7 Posts

Posted - 2004-05-05 : 13:06:29

quote:
Originally posted by drymchaser

quote:
Originally posted by mgn0528

Ok, Using your suggestions I've come up with the follwing user-defined function that will serve my needs. After looking at the requirements a bit closer I discovered a pattern or rule for the data.
date1 will always be less than or equal to date2; date2 will always be less than or equal to date3; etc. Dates not in use will be NULL. So, I feed the six date fields to the function and ask it to return the first date that is not NULL starting with date6 and working backwards. Here's the function:

If your rule is true MS has already created this function.

It is called COALESCE() and it returns the first non-NULL field in a list of fields COALESCE(date6, date5, date4,...)

re-he-he-he-he-eally? Well, I'll have to look into that. Maybe it's more efficient. Thanks for the info!

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-05-05 : 14:16:13

This works. I created a sample table and filled it with values to check it. To get the Max date out of the 6 columns I built a derived table that is the union of the 6 columns. I then took the max of the derived table to get the Max date per row. I hope this is what your looking for.

SELECT dates.cola,
dates.colb,

(SELECT MAX (theDates.SomeDate)
FROM (SELECT dates.date1 as SomeDate
UNION
SELECT dates.date2 as SomeDate
UNION
SELECT dates.date3 as SomeDate
UNION
SELECT dates.date4 as SomeDate
UNION
SELECT dates.date5 as SomeDate
UNION
SELECT dates.date6 as SomeDate) theDates) AS MaxDate
FROM dates

Dustin Michaels

mgn0528
Starting Member

7 Posts

Posted - 2004-05-07 : 10:43:26

Just an update. I wrote a little vb program using ado 2.8 to access a SQL Server 2000 database using both COALESCE and my Max_Date UDF just to see which was faster. Both return what I need. The results were interesting and, at the same time, ... not.

The test was setup to run 100 iterations of opening an ado recordset, selecting fields from my table using one of the functions, moving to the last record in the rs and closing the recordset. See the results in the image. This was pretty much what I got every time.

From the test you can see that overall there isn't much advantage to using one or the other (UDF vs T-SQL function) as they were pretty close - 9/100 of a second. However, what I did notice is that the UDF was more consistent taking about the same time +/- 1/100 of a second per iteration, where as the COALESCE function varied to a computationally significant degree from iteration to iteration.

File all of this info in the intersting insignificant facts drawer. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources