datepart

I am trying to use datepart to determine what row in a table a users
hiredate is closest to current system date
example
JOE was hired in Mar 01 2000
I need to get his payrate based off months experience
<12 months
<24 months
<60 months
<120 months
<200 months
Thanks
mike
something like below.
select hiredate, monthsrow from payee, payrategroup where
hiredate,
getdate(),
ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
+
ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
monthsrowHi
CREATE TABLE #Test
(
empl INT NOT NULL PRIMARY KEY,
hiredate DATETIME NOT NULL
)
INSERT INTO #Test VALUES (1,'20060101')
INSERT INTO #Test VALUES (2,'20060101')
INSERT INTO #Test VALUES (3,'20060409')
INSERT INTO #Test VALUES (4,'20060110')
INSERT INTO #Test VALUES (5,'20060112')
INSERT INTO #Test VALUES (6,'20060120')
INSERT INTO #Test VALUES (7,'20060108')
INSERT INTO #Test VALUES (8,'20060103')
DECLARE @.dt DATETIME
SET @.dt ='20060115' --desired date
SELECT TOP 1 WITH TIES *
FROM #Test WHERE hiredate>'20050101' AND hiredate < DATEADD(day,1,@.dt)
ORDER BY hiredate DESC
<ciojr@.yahoo.com> wrote in message
news:1144550863.151230.197030@.t31g2000cwb.googlegroups.com...
>I am trying to use datepart to determine what row in a table a users
> hiredate is closest to current system date
> example
> JOE was hired in Mar 01 2000
> I need to get his payrate based off months experience
> <12 months
> <24 months
> <60 months
> <120 months
> <200 months
> Thanks
> mike
> something like below.
> select hiredate, monthsrow from payee, payrategroup where
> hiredate,
> getdate(),
> ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
> +
> ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
> monthsrow
>|||Hi Mike,
Can you give the ddls and the expected output. The question seems to be
a bit confusing.|||not what I am looking for.