June 16, 2011 at 8:09 am
I have a table Customer which has following columns
Custid,PersonId,Startdate,Enddate,name,age .......
For each person there can be multiple rows in the table customer depending
on startdate and end date .I want to select one personid for each person from table customer,the one having maximum end date.
Consider the following example for table Customer
CustIdPersonIdstartdate Enddate Nameage ...
100101/12/201102/12/2011person1
101102/13/201104/14/2011person1
102202/11/201104/14/2011person2
103204/15/201105/12/2011person2
My query should give me
101102/13/201104/14/2011person1
103204/15/201105/12/2011person2
Please provide some help as to how to form the query for such a scenario
Thanks in advance
June 16, 2011 at 8:39 am
Something like this should work nicely
Select
t.CustId
,t.PersonId
,t.startdate
,t.Enddate
,t.Name
from
(
Select
CustId
,PersonId
,startdate
,Enddate
,Name
,ROW_NUMBER() OVER(PARTITION BY PersonId ORDER BY Enddate DESC) as rownum
from
table1
) as t
where
t.rownum=1
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 16, 2011 at 10:11 am
Another is to do an INNER JOIN to a subquery for the CustID and MAX(EndDate), joined on both fields.
A third method would be to put the above subquery into a CTE and join to the CTE.
June 16, 2011 at 11:00 am
Thanks a lot
June 16, 2011 at 11:21 am
It looks to me like Customer and Person data need to be separated in this, to make it work well.
I'd be willing to be that "name" and "age" really belong in a "People" table.
Normalizing the data that way will make everything you do with it easier.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply