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 2005 Forums
 Transact-SQL (2005)
 Stumped trying to get min values from a child tab

Author  Topic 

paulwfromtheden
Starting Member

9 Posts

Posted - 2009-12-11 : 09:01:12
Hi Guys,
Probably a bit of a novice question....
I have a view that for the sake of simplicity has four columns:=

UniqueID
PolicyID
CreateUser
CreateDate

There can be multiple rows for a given policy
I join from the Policy table where PolicyID is unique, I need a resultset of one row:=
PolicyID
EarliestCreateUser
EasliestCreateDate
NextEarliestCreateUser
NextEarliestCreateDate
It's simple to get the min(createdate) and CreateUser, but I also need to get the next earliest createdate and user.
I'm a bit stumped, can anyone help?
Many thanks
Paul

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-11 : 09:27:16
This?

declare @t table (UniqueID int identity(1, 1), PolicyID int, CreateUser int, CreateDate datetime)
insert @t
select 1, 1, getdate()
union all select 1, 2, getdate()-2
union all select 1, 3, getdate()-1
union all select 2, 2, getdate()
union all select 2, 1, getdate()-1
union all select 3, 1, getdate()

; with t1 as (select *, row_number() over (partition by PolicyID order by CreateDate) as row from @t)
select PolicyID,
max(case when row = 1 then CreateUser end) as EarliestCreateUser,
max(case when row = 1 then CreateDate end) as EasliestCreateDate,
max(case when row = 2 then CreateUser end) as NextEarliestCreateUser,
max(case when row = 2 then CreateDate end) as NextEasliestCreateDate
from t1 group by PolicyID


Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

paulwfromtheden
Starting Member

9 Posts

Posted - 2009-12-11 : 09:48:51
That is VERY clever, I need a lie down...
Thanks very much
Paul
Go to Top of Page
   

- Advertisement -