473,847 Members | 1,714 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help selecting the proper child record

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
4 1460
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(PersonNa me) values ('Fred')
insert Person(PersonNa me) values ('Barney')
insert Person(PersonNa me) values ('Wilma')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,10,'1/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,11,'2/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,12,'3/1/2005')

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

insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,10,'1/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,11,'2/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,12,'3/1/2005')
select p.PersonID, p.PersonName, pp.EffectiveDat e, pp.RateID, pp.Rate
from Person p
inner join (select pr.PersonID, pr.EffectiveDat e, 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_**********@h otmail.com> wrote in message
news:Z1******** **********@news svr25.news.prod igy.net...
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
CK
See changes below. Also not every Person has a PersonRateRecor d but I need
all the persons so I am thinking LEFT OUTER JOIN on PersonRate.

Thanks for the pointers!!

"DickChrist oph" <dc********@yah oo.com> wrote in message
news:Xu******** **********@torn ado.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( *****PersonRate ID 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(PersonNa me) values ('Fred')
insert Person(PersonNa me) values ('Barney')
insert Person(PersonNa me) values ('Wilma')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,10,'1/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,11,'2/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,12,'3/1/2005')

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

insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,10,'1/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,11,'2/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,12,'3/1/2005')
select p.PersonID, p.PersonName, pp.EffectiveDat e, pp.RateID, pp.Rate
from Person p
inner join (select pr.PersonID, pr.EffectiveDat e, 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_**********@h otmail.com> wrote in message
news:Z1******** **********@news svr25.news.prod igy.net...
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
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(PersonNa me) values ('Fred')
insert Person(PersonNa me) values ('Barney')
insert Person(PersonNa me) values ('Wilma')

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

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

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

--No PersonRate For Wilma
select p.PersonID, p.PersonName, pp.EffectiveDat e, pp.RateID, pp.RateType,
pp.Rate
from Person p
left outer join (select pr.PersonID, pr.EffectiveDat e, 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_**********@h otmail.com> wrote in message
news:5H******** ***********@new ssvr21.news.pro digy.com...
See changes below. Also not every Person has a PersonRateRecor d but I
need all the persons so I am thinking LEFT OUTER JOIN on PersonRate.

Thanks for the pointers!!

"DickChrist oph" <dc********@yah oo.com> wrote in message
news:Xu******** **********@torn ado.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(

*****PersonRate ID 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(PersonNa me) values ('Fred')
insert Person(PersonNa me) values ('Barney')
insert Person(PersonNa me) values ('Wilma')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,10,'1/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,11,'2/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,12,'3/1/2005')

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

insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,10,'1/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,11,'2/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,12,'3/1/2005')
select p.PersonID, p.PersonName, pp.EffectiveDat e, pp.RateID, pp.Rate
from Person p
inner join (select pr.PersonID, pr.EffectiveDat e, 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_**********@h otmail.com> wrote in message
news:Z1******** **********@news svr25.news.prod igy.net...
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
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.EffectiveDat e, 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.CandidateRat eID 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.JobC ategory jc ON jc.JobCatID = CR.JobCatID

"DickChrist oph" <dc********@yah oo.com> wrote in message
news:dY******** **********@torn ado.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(PersonNa me) values ('Fred')
insert Person(PersonNa me) values ('Barney')
insert Person(PersonNa me) values ('Wilma')

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

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

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

--No PersonRate For Wilma
select p.PersonID, p.PersonName, pp.EffectiveDat e, pp.RateID, pp.RateType,
pp.Rate
from Person p
left outer join (select pr.PersonID, pr.EffectiveDat e, 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_**********@h otmail.com> wrote in message
news:5H******** ***********@new ssvr21.news.pro digy.com...
See changes below. Also not every Person has a PersonRateRecor d but I
need all the persons so I am thinking LEFT OUTER JOIN on PersonRate.

Thanks for the pointers!!

"DickChrist oph" <dc********@yah oo.com> wrote in message
news:Xu******** **********@torn ado.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(

*****PersonRate ID 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(PersonNa me) values ('Fred')
insert Person(PersonNa me) values ('Barney')
insert Person(PersonNa me) values ('Wilma')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,10,'1/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,11,'2/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(1,12,'3/1/2005')

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

insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,10,'1/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,11,'2/1/2005')
insert PersonRate(Pers onID, Rate, EffectiveDate) values(3,12,'3/1/2005')
select p.PersonID, p.PersonName, pp.EffectiveDat e, pp.RateID, pp.Rate
from Person p
inner join (select pr.PersonID, pr.EffectiveDat e, 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_**********@h otmail.com> wrote in message
news:Z1******** **********@news svr25.news.prod igy.net...
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3500
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 Pentagjetvedeh karuvificials madhla reachathe strategy in karkun campaign deshatinst terrorism. "mudivae maretu winning or losing karkun global varti jetvedeh terror?" Mr. Rumsfeld adugued in a recent memormariyuum. vede velli jetvedeh madhla...
4
2745
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 other details, including videos. For arguments sake, the first person is a parent, the second is the child, and the videos belong to the child. The problem that I am having is that on the form I can manage to get the details of the parent on it...
4
1987
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. The sub form stores the time sheet data for each employee.When I am able to do the timesheet data entry for the first record.But when I select the second employee from the dropdown list and click on the subform for entering the timesheet data, it...
2
2047
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 second field (Prospect Name). The user then uses the subform to enter a document location. The problem is this:
4
9024
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, even the removal, I just don't know how to keep track of the parent, so that I can set its child to the child of the node to be removed. IE - if I had C / \ B D
1
1351
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 parameter the user has entered in the parent element? For example: In the instance document I have the following...
2
3338
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 certain number of them. Firstly I identify all the parent records that have the requird number of child records and insert them into the result table.
15
2588
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 determine who needs to receive the text message then send the message to the address. Only problem is, the employee may receive up to 4 of the same messages because each thread gets the recors then sends the message. I need somehow to prevent...
2
1147
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 corresponding classes for each table. Each class would have functions Insert, Edit, Delete I would like to find some information how to properly design my classes. I used to program in php and delphi.
0
9886
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10983
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10706
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10338
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9482
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7882
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7056
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5719
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4528
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.