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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • daJunkCollector
    New Member
    • Jun 2007
    • 76

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

    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!

    Code:
    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
    Recognized Expert Specialist
    • Jun 2007
    • 2878

    #2
    Originally posted by 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!

    Code:
    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

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

    -- CK

    Comment

    • daJunkCollector
      New Member
      • Jun 2007
      • 76

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

      Here is the exact Query:

      Code:
      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.

      Comment

      • ck9663
        Recognized Expert Specialist
        • Jun 2007
        • 2878

        #4
        Originally posted by daJunkCollector
        CK, thank you very much for helping me. Unfortunately, I am still getting the exact same result set.

        Here is the exact Query:

        Code:
        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(datetim e, CONVERT(varchar (4),@RequestYea r)), 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

        Comment

        • daJunkCollector
          New Member
          • Jun 2007
          • 76

          #5
          Yes that is correct.

          Comment

          • daJunkCollector
            New Member
            • Jun 2007
            • 76

            #6
            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:

            Code:
            SELECT SUM(VacationHours) AS adjustedVacation
                    FROM HR_VacRequest
                    WHERE RequestedBy = @EmployeeID AND RequestYear = @RequestYear
            Code:
            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

            Comment

            • ck9663
              Recognized Expert Specialist
              • Jun 2007
              • 2878

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

              -- CK

              Comment

              Working...