By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Help selecting the proper child record

P: n/a
CK
Good Morning,
I have a person table with personID. I have a personRate table with
personID, rateID, and effectiveDate.
I need to select fields from personRate, but I want the fields from the
proper record.

I need the one child record that has the most current date of the largest
rateID.

For example a person may have many rate records. I need the record that has
the most current date of the largest rateID they have. Does that make
sense?

I am making a view that has data from both tables. I need to display the
most current rate info.

Any ideas? TIA ~ CK
Mar 23 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Hi CK,

I kind of guessed on the DDL but something like the select statement below
(after the Creates and Inserts) should do it for you.

Create Table Person(
PersonID int identity(1,1) Primary Key,
PersonName varchar(50))

Create Table PersonRate(
RateID int identity(1,1) Primary Key,
PersonID int references Person(PersonID),
Rate decimal(9,2),
EffectiveDate smalldatetime)
insert Person(PersonName) values ('Fred')
insert Person(PersonName) values ('Barney')
insert Person(PersonName) values ('Wilma')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')
select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
from Person p
inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
from PersonRate pr
inner join (select PersonID, Max(RateID) as MaxRateID
from PersonRate
group by PersonID) pr1
on pr.personid = pr1.personid
and pr.rateid = pr1.maxRateID) pp
on p.PersonID = pp.Personid

--
-Dick Christoph
"CK" <c_**********@hotmail.com> wrote in message
news:Z1******************@newssvr25.news.prodigy.n et...
Good Morning,
I have a person table with personID. I have a personRate table with
personID, rateID, and effectiveDate.
I need to select fields from personRate, but I want the fields from the
proper record.

I need the one child record that has the most current date of the largest
rateID.

For example a person may have many rate records. I need the record that
has the most current date of the largest rateID they have. Does that make
sense?

I am making a view that has data from both tables. I need to display the
most current rate info.

Any ideas? TIA ~ CK

Mar 23 '06 #2

P: n/a
CK
See changes below. Also not every Person has a PersonRateRecord but I need
all the persons so I am thinking LEFT OUTER JOIN on PersonRate.

Thanks for the pointers!!

"DickChristoph" <dc********@yahoo.com> wrote in message
news:Xu******************@tornado.rdc-kc.rr.com...
Hi CK,

I kind of guessed on the DDL but something like the select statement below
(after the Creates and Inserts) should do it for you.

Create Table Person(
PersonID int identity(1,1) Primary Key,
PersonName varchar(50))

Create Table PersonRate( *****PersonRateID int identity(1,1) Primary Key,
***** RateID int references Rate(RateID), PersonID int references Person(PersonID),
Rate decimal(9,2),
EffectiveDate smalldatetime)

Create Table Rate(
RateID int identity(1,1) Primary Key,
RateType varchar(20))

insert Person(PersonName) values ('Fred')
insert Person(PersonName) values ('Barney')
insert Person(PersonName) values ('Wilma')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')
select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
from Person p
inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
from PersonRate pr
inner join (select PersonID, Max(RateID) as MaxRateID
from PersonRate
group by PersonID) pr1
on pr.personid = pr1.personid
and pr.rateid = pr1.maxRateID) pp
on p.PersonID = pp.Personid

--
-Dick Christoph
"CK" <c_**********@hotmail.com> wrote in message
news:Z1******************@newssvr25.news.prodigy.n et...
Good Morning,
I have a person table with personID. I have a personRate table with
personID, rateID, and effectiveDate.
I need to select fields from personRate, but I want the fields from the
proper record.

I need the one child record that has the most current date of the largest
rateID.

For example a person may have many rate records. I need the record that
has the most current date of the largest rateID they have. Does that
make sense?

I am making a view that has data from both tables. I need to display the
most current rate info.

Any ideas? TIA ~ CK


Mar 23 '06 #3

P: n/a
Hi CK

Alrighty then how about this. By the way I think the PersonRate.Rate column
is not where it should be. Probably it belongs in the Rate table but I don't
know what that column really means. You left it in PersonRate so I left it
in PersonRate.

Create Table Person(
PersonID int identity(1,1) Primary Key,
PersonName varchar(50))

Create Table Rate(
RateID int identity(1,1) Primary Key,
RateType varchar(20))

Create Table PersonRate(
PersonRateID int identity(1,1) Primary Key,
RateID int references Rate(RateID),
PersonID int references Person(PersonID),
Rate decimal(9,2),
EffectiveDate smalldatetime)
insert Person(PersonName) values ('Fred')
insert Person(PersonName) values ('Barney')
insert Person(PersonName) values ('Wilma')

insert Rate(RateType) values ('A')
insert Rate(RateType) values ('B')
insert Rate(RateType) values ('C')

--Fred
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(1,1,10,'1/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(2,1,11,'2/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(3,1,12,'3/1/2005')

--Barney
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(1,2,10,'1/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(2,2,11,'2/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(3,2,12,'3/1/2005')

--No PersonRate For Wilma
select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.RateType,
pp.Rate
from Person p
left outer join (select pr.PersonID, pr.EffectiveDate, pr.RateID,
r.RateType, pr.Rate
from PersonRate pr
inner join Rate r
on pr.RateID = r.RateID
inner join (select PersonID, Max(RateID) as MaxRateID
from PersonRate
group by PersonID) pr1
on pr.personid = pr1.personid
and pr.rateid = pr1.maxRateID) pp
on p.PersonID = pp.Personid

--
-Dick Christoph
dc******@mn.rr.com
612-724-9282
"CK" <c_**********@hotmail.com> wrote in message
news:5H*******************@newssvr21.news.prodigy. com...
See changes below. Also not every Person has a PersonRateRecord but I
need all the persons so I am thinking LEFT OUTER JOIN on PersonRate.

Thanks for the pointers!!

"DickChristoph" <dc********@yahoo.com> wrote in message
news:Xu******************@tornado.rdc-kc.rr.com...
Hi CK,

I kind of guessed on the DDL but something like the select statement
below (after the Creates and Inserts) should do it for you.

Create Table Person(
PersonID int identity(1,1) Primary Key,
PersonName varchar(50))

Create Table PersonRate(

*****PersonRateID int identity(1,1) Primary Key,
***** RateID int references Rate(RateID),
PersonID int references Person(PersonID),
Rate decimal(9,2),
EffectiveDate smalldatetime)


Create Table Rate(
RateID int identity(1,1) Primary Key,
RateType varchar(20))

insert Person(PersonName) values ('Fred')
insert Person(PersonName) values ('Barney')
insert Person(PersonName) values ('Wilma')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')
select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
from Person p
inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
from PersonRate pr
inner join (select PersonID, Max(RateID) as MaxRateID
from PersonRate
group by PersonID) pr1
on pr.personid = pr1.personid
and pr.rateid = pr1.maxRateID) pp
on p.PersonID = pp.Personid

--
-Dick Christoph
"CK" <c_**********@hotmail.com> wrote in message
news:Z1******************@newssvr25.news.prodigy.n et...
Good Morning,
I have a person table with personID. I have a personRate table with
personID, rateID, and effectiveDate.
I need to select fields from personRate, but I want the fields from the
proper record.

I need the one child record that has the most current date of the
largest rateID.

For example a person may have many rate records. I need the record that
has the most current date of the largest rateID they have. Does that
make sense?

I am making a view that has data from both tables. I need to display
the most current rate info.

Any ideas? TIA ~ CK



Mar 23 '06 #4

P: n/a
CK
Rate is a $ amount , rateType is string indicating what the $ amount is for.
Like a "raise", "offer" , "request". Just some entities this company uses.
Yes it is in the correct place. Thanks a lot. Max didn't give me the desired
result. I use SELECT TOP 1.

here's what worked. I added a couple other tables as i needed values from
them as well

SELECT vC.*, CR.EffectiveDate, rt.TypeName, jc.CategoryName,
CASE
WHEN CR.Rate IS NULL THEN CAST('None' AS char(4))
ELSE CAST(CR.Rate AS varchar(6))
END AS Rate
FROM vCandidates vC
LEFT OUTER JOIN CandidateRate CR
ON vC.CandidateID = CR.CandidateID
AND CR.CandidateRateID IN
(
SELECT TOP 1 CandidateRateID
FROM CandidateRate
WHERE RateTypeID < 4 AND CandidateID = CR.CandidateID
ORDER BY RateTypeID DESC, EffectiveDate DESC
)
LEFT OUTER JOIN RateType rt ON rt.RateTypeID = CR.RateTypeID
LEFT OUTER JOIN Innova.dbo.JobCategory jc ON jc.JobCatID = CR.JobCatID

"DickChristoph" <dc********@yahoo.com> wrote in message
news:dY******************@tornado.rdc-kc.rr.com...
Hi CK

Alrighty then how about this. By the way I think the PersonRate.Rate
column is not where it should be. Probably it belongs in the Rate table
but I don't know what that column really means. You left it in PersonRate
so I left it in PersonRate.

Create Table Person(
PersonID int identity(1,1) Primary Key,
PersonName varchar(50))

Create Table Rate(
RateID int identity(1,1) Primary Key,
RateType varchar(20))

Create Table PersonRate(
PersonRateID int identity(1,1) Primary Key,
RateID int references Rate(RateID),
PersonID int references Person(PersonID),
Rate decimal(9,2),
EffectiveDate smalldatetime)
insert Person(PersonName) values ('Fred')
insert Person(PersonName) values ('Barney')
insert Person(PersonName) values ('Wilma')

insert Rate(RateType) values ('A')
insert Rate(RateType) values ('B')
insert Rate(RateType) values ('C')

--Fred
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(1,1,10,'1/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(2,1,11,'2/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(3,1,12,'3/1/2005')

--Barney
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(1,2,10,'1/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(2,2,11,'2/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(3,2,12,'3/1/2005')

--No PersonRate For Wilma
select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.RateType,
pp.Rate
from Person p
left outer join (select pr.PersonID, pr.EffectiveDate, pr.RateID,
r.RateType, pr.Rate
from PersonRate pr
inner join Rate r
on pr.RateID = r.RateID
inner join (select PersonID, Max(RateID) as MaxRateID
from PersonRate
group by PersonID) pr1
on pr.personid = pr1.personid
and pr.rateid = pr1.maxRateID) pp
on p.PersonID = pp.Personid

--
-Dick Christoph
dc******@mn.rr.com
612-724-9282
"CK" <c_**********@hotmail.com> wrote in message
news:5H*******************@newssvr21.news.prodigy. com...
See changes below. Also not every Person has a PersonRateRecord but I
need all the persons so I am thinking LEFT OUTER JOIN on PersonRate.

Thanks for the pointers!!

"DickChristoph" <dc********@yahoo.com> wrote in message
news:Xu******************@tornado.rdc-kc.rr.com...
Hi CK,

I kind of guessed on the DDL but something like the select statement
below (after the Creates and Inserts) should do it for you.

Create Table Person(
PersonID int identity(1,1) Primary Key,
PersonName varchar(50))

Create Table PersonRate(

*****PersonRateID int identity(1,1) Primary Key,
***** RateID int references Rate(RateID),
PersonID int references Person(PersonID),
Rate decimal(9,2),
EffectiveDate smalldatetime)


Create Table Rate(
RateID int identity(1,1) Primary Key,
RateType varchar(20))

insert Person(PersonName) values ('Fred')
insert Person(PersonName) values ('Barney')
insert Person(PersonName) values ('Wilma')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')
select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
from Person p
inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
from PersonRate pr
inner join (select PersonID, Max(RateID) as MaxRateID
from PersonRate
group by PersonID) pr1
on pr.personid = pr1.personid
and pr.rateid = pr1.maxRateID) pp
on p.PersonID = pp.Personid

--
-Dick Christoph
"CK" <c_**********@hotmail.com> wrote in message
news:Z1******************@newssvr25.news.prodigy.n et...
Good Morning,
I have a person table with personID. I have a personRate table with
personID, rateID, and effectiveDate.
I need to select fields from personRate, but I want the fields from the
proper record.

I need the one child record that has the most current date of the
largest rateID.

For example a person may have many rate records. I need the record that
has the most current date of the largest rateID they have. Does that
make sense?

I am making a view that has data from both tables. I need to display
the most current rate info.

Any ideas? TIA ~ CK



Mar 23 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.