By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,361 Members | 1,342 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,361 IT Pros & Developers. It's quick & easy.

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

daJunkCollector
P: 76
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!

Expand|Select|Wrap|Line Numbers
  1. SELECT Value ,MAX(Date)
  2.     FROM myTable
  3.     WHERE ID= '565'
  4.     GROUP BY Value
  5.     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.
Apr 4 '08 #1
Share this Question
Share on Google+
6 Replies


ck9663
Expert 2.5K+
P: 2,878
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!

Expand|Select|Wrap|Line Numbers
  1. SELECT Value ,MAX(Date)
  2.     FROM myTable
  3.     WHERE ID= '565'
  4.     GROUP BY Value
  5.     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

Expand|Select|Wrap|Line Numbers
  1. select * from 
  2. (SELECT Value ,MAX(Date) as MaxDate
  3.     FROM myTable
  4.     WHERE ID= '565'
  5.     GROUP BY Value) MySubquery
  6. Where MaxDate <= @Variable

-- CK
Apr 4 '08 #2

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

Here is the exact Query:

Expand|Select|Wrap|Line Numbers
  1. DECLARE @RequestYear  smallint
  2. DECLARE @EmployeeID   int
  3. SET @RequestYear = 2002
  4. SET @EmployeeID = 565
  5. SELECT *
  6.     FROM
  7.         (SELECT VacationHours, MAX(EffectiveDate) as MaxDate
  8.             FROM HRE_T_VAC_AvailableVacation
  9.             WHERE EmployeeID = @EmployeeID
  10.             GROUP BY VacationHours) mySubQuery
  11.     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.
Apr 4 '08 #3

ck9663
Expert 2.5K+
P: 2,878
CK, thank you very much for helping me. Unfortunately, I am still getting the exact same result set.

Here is the exact Query:

Expand|Select|Wrap|Line Numbers
  1. DECLARE @RequestYear  smallint
  2. DECLARE @EmployeeID   int
  3. SET @RequestYear = 2002
  4. SET @EmployeeID = 565
  5. SELECT *
  6.     FROM
  7.         (SELECT VacationHours, MAX(EffectiveDate) as MaxDate
  8.             FROM HRE_T_VAC_AvailableVacation
  9.             WHERE EmployeeID = @EmployeeID
  10.             GROUP BY VacationHours) mySubQuery
  11.     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
Apr 4 '08 #4

daJunkCollector
P: 76
Yes that is correct.
Apr 4 '08 #5

daJunkCollector
P: 76
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT SUM(VacationHours) AS adjustedVacation
  2.         FROM HR_VacRequest
  3.         WHERE RequestedBy = @EmployeeID AND RequestYear = @RequestYear
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     av.VacationHours AS totalVacation
  3.         FROM HRE_T_VAC_AvailableVacation av
  4.             INNER JOIN (SELECT MAX(EffectiveDate) AS MaxEffectiveDate 
  5.                             FROM HRE_T_VAC_AvailableVacation
  6.                             WHERE EmployeeID=@EmployeeID
  7.                                 AND EffectiveDate<=CASE WHEN DATEPART(yy,GETDATE())=@RequestYear THEN GETDATE() ELSE '12/31/'+CONVERT(varchar(4),@RequestYear)+' 23:59:59' END
  8.                        ) t ON av.EffectiveDate=t.MaxEffectiveDate
  9.         WHERE EmployeeID=@EmployeeID
Apr 4 '08 #6

ck9663
Expert 2.5K+
P: 2,878
Could you post some test data and what you're trying to attain?

-- CK
Apr 4 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.