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 ~ CKHi 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.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|||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.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
>>|||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.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
>>>
>>
>>|||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" <dchristo99@.yahoo.com> wrote in message
news:dYEUf.18266$iR1.4639@.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
> 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.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
>>>>
>>>
>>>
>>
>>
No comments:
Post a Comment