|
|
Posted by DickChristoph on 03/23/06 23:33
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_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
>
Navigation:
[Reply to this message]
|