Help selecting the proper child record

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

Subscribe Post Reply

1409

DickChristoph

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

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

DickChristoph

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

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

Similar topics

java help needed

by: abcd | last post by:

kutthaense Secretary Djetvedehald H. Rumsfeld legai predicted eventual vicmadhlary in Iraq mariyu Afghmadhlaistmadhla, kaani jetvedehly after "a ljetvedehg, hard slog," mariyu vede legai pressed...

Java

Access database help

by: armitageshanks | last post by:

Hi, I'm pretty new too Access 2000 and I have a database that I with the details of peoples names, i.e. surname, forename, ref number. I have another table with surname, forename, etc plus some...

Microsoft Access / VBA

error when selecting data from the dropdown list

by: Doslil | last post by:

I have a form which has a subform.In the main form I have only one field (this is a drop down list and has a query attached to it) which selects empno,Name from the EmployeeInformation table. ...

Microsoft Access / VBA

Data Entry Form problem. Please help!!

by: edworboys | last post by:

I have designed a data entry form with a number of fields and a sub form. The first field (Country) is a combo box and the user selects a country. This, in turn reduces the number of options in the...

Microsoft Access / VBA

binary search tree removal - stuck - HELP!!

by: Tarique Jawed | last post by:

Alright I needed some help regarding a removal of a binary search tree. Yes its for a class, and yes I have tried working on it on my own, so no patronizing please. I have most of the code working,...

C / C++

HELP - How to restrict elements in instance doc?

by: da_pander | last post by:

Hi, Can anyone tell me if it is possible to create a XSD schema document that will only allow certain elements to be displayed as available within the instance document depending on say a...

.NET Framework

Selecting TOP X child records for a parent record

by: Catch_22 | last post by:

Hi, I have a stored procedure that has to extract the child records for particular parent records. The issue is that in some cases I do not want to extract all the child records only a...

Microsoft SQL Server

Help - Timing Logic

by: Jay | last post by:

I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to...

Visual Basic .NET

Proper Design

by: John | last post by:

Hi there, I am writing a simple program that will connect to database. Database has 2 tables, let's call them father and child. This is one to many relationship. I would like to create...

Visual Basic .NET

How to Become a Cyber Security Engineer

by: Rina0 | last post by:

Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...

Security

Access Europe meeting on Wed 2 August - An Access Developer Guide to creating applications in Blazor

by: isladogs | last post by:

The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...

Microsoft Access / VBA

How to Use CodiumAI's pr-agent?

by: erikbower65 | last post by:

Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...

General

How to insert a new record in a database

by: Taofi | last post by:

I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...

DB2 Database

Cancel Print Action if Sub form has No Records

by: DJRhino1175 | last post by:

When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...

Microsoft Access / VBA

To verify entry of a dropdown

by: DJRhino | last post by:

Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...

Microsoft Access / VBA

React to native button blinking effect

by: lllomh | last post by:

Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{

Software Development

How does React native implement an English player?

by: lllomh | last post by:

How does React native implement an English player?

Mobile Development

Windows 11 and Access Combo Box Issue

by: DJRhino | last post by:

Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

Microsoft Access / VBA