|
|
Posted by DickChristoph on 03/24/06 00:04
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
dchristo@mn.rr.com
612-724-9282
"CK" <c_kettenbach@hotmail.com> wrote in message
news:5HEUf.62662$dW3.28210@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" <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
>>>
>>
>>
>
>
Navigation:
[Reply to this message]
|