Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select query help Expand / Collapse
Author
Message
Posted Thursday, June 16, 2011 8:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 05, 2012 7:17 AM
Points: 54, Visits: 136
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

CustId PersonId startdate Enddate Name age ...

100 1 01/12/2011 02/12/2011 person1

101 1 02/13/2011 04/14/2011 person1

102 2 02/11/2011 04/14/2011 person2

103 2 04/15/2011 05/12/2011 person2

My query should give me

101 1 02/13/2011 04/14/2011 person1

103 2 04/15/2011 05/12/2011 person2

Please provide some help as to how to form the query for such a scenario



Thanks in advance
Post #1126587
Posted Thursday, June 16, 2011 8:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:10 AM
Points: 822, Visits: 1,198
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.
Post #1126628
Posted Thursday, June 16, 2011 10:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 7,053, Visits: 6,214
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1126731
Posted Thursday, June 16, 2011 11:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 05, 2012 7:17 AM
Points: 54, Visits: 136
Thanks a lot
Post #1126779
Posted Thursday, June 16, 2011 11:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1126791
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse