Posted by CK on 03/23/06 23:46

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" <dchristo99@yahoo.com> wrote in message
news:XuEUf.18265$iR1.4137@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_kettenbach@hotmail.com> wrote in message
> news:Z1CUf.58897$Jd.37489@newssvr25.news.prodigy.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
>>
>
>

[Back to original message]