|
|
Posted by CK on 03/24/06 00:19
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.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]
|