Code Listed: select max value in row that is less than my variable

[daJunkCollector]

Hey, I am having a problem with a SQL statement. I hope someone can help me.

I have a table with one key column, ID and two columns, Value and Date. I have a variable that contains a certain date. I need to select the max value whose date is less than my variable!

SELECT Value ,MAX(Date)
    FROM myTable
    WHERE ID= '565'
    GROUP BY Value
    HAVING MAX(Date) <= @Variable

This gives me ALL the values less than my variable, not just the max value less than my variable :(. Please help.

[ck9663]

Hey, I am having a problem with a SQL statement. I hope someone can help me.

I have a table with one key column, ID and two columns, Value and Date. I have a variable that contains a certain date. I need to select the max value whose date is less than my variable!

SELECT Value ,MAX(Date)
    FROM myTable
    WHERE ID= '565'
    GROUP BY Value
    HAVING MAX(Date) <= @Variable

This gives me ALL the values less than my variable, not just the max value less than my variable :(. Please help.

Try using a subquery

select * from 
(SELECT Value ,MAX(Date) as MaxDate
    FROM myTable
    WHERE ID= '565'
    GROUP BY Value) MySubquery
Where MaxDate <= @Variable

-- CK

[daJunkCollector]

CK, thank you very much for helping me. Unfortunately, I am still getting the exact same result set.

Here is the exact Query:

DECLARE @RequestYear  smallint
DECLARE @EmployeeID   int
SET @RequestYear = 2002
SET @EmployeeID = 565
SELECT *
    FROM
        (SELECT VacationHours, MAX(EffectiveDate) as MaxDate
            FROM HRE_T_VAC_AvailableVacation
            WHERE EmployeeID = @EmployeeID
            GROUP BY VacationHours) mySubQuery
    WHERE MaxDate <= CONVERT(datetime, CONVERT(varchar(4),@RequestYear))

It is still returning all rows with dates earlier than 2002 rather than just the most recent row prior to 2002.

[ck9663]

CK, thank you very much for helping me. Unfortunately, I am still getting the exact same result set.

Here is the exact Query:

DECLARE @RequestYear  smallint
DECLARE @EmployeeID   int
SET @RequestYear = 2002
SET @EmployeeID = 565
SELECT *
    FROM
        (SELECT VacationHours, MAX(EffectiveDate) as MaxDate
            FROM HRE_T_VAC_AvailableVacation
            WHERE EmployeeID = @EmployeeID
            GROUP BY VacationHours) mySubQuery
    WHERE MaxDate <= CONVERT(datetime, CONVERT(varchar(4),@RequestYear))

It is still returning all rows with dates earlier than 2002 rather than just the most recent row prior to 2002.

Your EffectiveDate field, I assume is a datetime column. After the subquery, executed, the Max() of EffectiveDate will be called MaxDate which is also a datetime. Your condition CONVERT(datetime, CONVERT(varchar(4),@RequestYear)), you are passing a year on the convert function and expecting a datetime to be returned. It will always return the first day of that year. Is that what you're trying to do?

-- CK

[daJunkCollector]

Yes that is correct.

[daJunkCollector]

Ok....if you're still with me....I made a little progress. I designed to SELECT queries. Problem is...I need to somehow combine them so that I can select both values:

SELECT SUM(VacationHours) AS adjustedVacation
        FROM HR_VacRequest
        WHERE RequestedBy = @EmployeeID AND RequestYear = @RequestYear

SELECT
    av.VacationHours AS totalVacation
        FROM HRE_T_VAC_AvailableVacation av
            INNER JOIN (SELECT MAX(EffectiveDate) AS MaxEffectiveDate 
                            FROM HRE_T_VAC_AvailableVacation
                            WHERE EmployeeID=@EmployeeID
                                AND EffectiveDate<=CASE WHEN DATEPART(yy,GETDATE())=@RequestYear THEN GETDATE() ELSE '12/31/'+CONVERT(varchar(4),@RequestYear)+' 23:59:59' END
                       ) t ON av.EffectiveDate=t.MaxEffectiveDate
        WHERE EmployeeID=@EmployeeID

[ck9663]

Could you post some test data and what you're trying to attain?

-- CK