Picking a Max value from multiple fields

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 2008 Forums

Transact-SQL (2008)

Picking a Max value from multiple fields

Author

Topic

Vic_M
Starting Member

2 Posts

Posted - 2011-04-15 : 14:50:22

Hello,

I am trying to build a Last_Activity_Date. Current Select statement looks something like this

>Select Job_Number, Last_Cost_Date, Last_Payroll_Date, Last_Bill_Date

>From Job_Master_Total

Is there a way to include create a field that will pick a Max date from the three date fields in the above select?

I have a current workaround by building a long CASE WHEN instruction (not shown in above select) that compares all variables to come up with a Last_Activity_Date. I am hoping for something more efficient.

Please help.

Thank you

Vic M

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-15 : 14:59:58

You could try something like this:

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-15 : 15:04:07

...

not sure if its worth it...
but:


Declare @t table (
	id int identity(1,1) not null,
	d1 datetime,
	d2 datetime,
	d3 datetime,
	Primary Key(id)
)

Insert Into @t Select '1/1/2011', '2/1/2011', '3/1/2011'
Insert Into @t Select '1/1/2011', '4/1/2011', '3/1/2011'
Insert Into @t Select '5/1/2011', '2/1/2011', '3/1/2011'
Insert Into @t Select '1/1/2011', '2/1/2011', null
Insert Into @t Select null, '2/1/2011', null
Insert Into @t Select null, null, null

Select 
	A.*,
	B.LastD
From @t A
Left Join
	(
	Select 
		id, 
		LastD = max(d)
	From
		(
		Select *
		From @t A
		Unpivot (d For d_Source In (d1, d2, d3)) B 
		) Z
	Group By id
	) B
On A.id = B.id

Corey

I Has Returned!!

Vic_M
Starting Member

2 Posts

Posted - 2011-04-15 : 16:21:14

I was reading your replies to my question and I see great information. Especially if I have 5+ multiple rows to evaluate. However I reconsidered my original work around to may be work a litter better with the following code:

==================================================================================
----EVALUATION (FINAL) FOR EVALUATION 3 VARIABLES FOR MAX VALUE
SELECT JOB_NUMBER, CASE WHEN LAST_PAYROLL_DATE>=VARIABLE_EVAL_1.EVALUATION_1
THEN LAST_PAYROLL DATE
ELSE VARIABLE_EVAL_2.EVALUATION_2
END AS EVALUATION_FINAL
FROM JOB_MASTER_TOTAL

LEFT OUTR JOIN

----EVALUATION 1
(SELECT JOB_NUMBER, CASE WHEN LAST_BILLING_DATE>=LAST_PAYROLL_DATE
THEN LAST_BILLING_DATE
ELSE LAST_PAYROLL_DATE
END AS EVALUATION_1
FROM JOB_MASTER_TOTAL AS TOTAL_1 ) AS VARIABLE_EVAL_1

ON JOB_MASTER_TOTAL.JOB_NUMBER=VARIABLE_EVAL_1.JOB_NUMBER

===========================================================
I think that should work better that a single massing CASE WHEN.

Thank you

Vic M

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources