Lowest-date from a group - 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)

Lowest-date from a group

Author

Topic

ny156uk
Starting Member

1 Post

Posted - 2006-07-12 : 11:10:20

Hi,

I have a list of Pols with a series of dates, and I need to calculate and populate "FirstStop"

Pol FirstReq CheckComp GoAhead FirstStop
1 01/05/06 01/04/06 01/03/06 (Would = GoAhead)
2 01/03/06 01/04/06 01/06/06 (would = FirstReq)

I have an Access database we are migrating from which does this using a function (see below)that when 'nested' assesses and returns the lowest of the 3 values.

(access function I use):
If IsNull(firstDate) Then Lowest = secondDate Else If IsNull(secondDate) Then Lowest = firstDate Else If firstDate < secondDate Then Lowest = firstDate Else Lowest = secondDate

I would love to be able to get SQL-Server to assess the 3 columns, find the lowest date and return that as "FirstStop".

Any help would be hugely appreciated, i've scoured quite a few sites trying to find the answer already without luck.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-12 : 11:17:37

[code]declare @table table
(
Pol int,
FirstReq datetime,
CheckComp datetime,
GoAhead datetime
)

insert into @table
select 1, '01/05/06', '01/04/06', '01/03/06' union all
select 2, '01/03/06', '01/04/06', '01/06/06'

select *,
FirstStop = case when FirstReq > CheckComp then
case when CheckComp > GoAhead then
GoAhead
else
CheckComp
end
else
case when FirstReq > GoAhead then
GoAhead
else
FirstReq
end
end
from @table

/* RESULT
Pol FirstReq CheckComp GoAhead FirstStop
---- ----------- ----------- ----------- -----------
1 2006-01-05 2006-01-04 2006-01-03 2006-01-03
2 2006-01-03 2006-01-04 2006-01-06 2006-01-03
*/
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources