SQL function returns wrong value

Question

I have a function that takes 2 parameters: @iEmployeeID and @dDate.

It's purpose is to find a budget rate for the given parameters. In other words, it should find the largest date being smaller or equal to the @dDate argument, and return the rate that corresponds.

Budget rates are:

Start        Rate
-------      -----
01-01-2008   600
01-01-2009   800
01-01-2010   700

DECLARE @result decimal(38,20)

SELECT @result = decRate
FROM BudgetRates BR
WHERE BR.iRefEmployeeID = @iEmployeeID
GROUP BY decRate
HAVING MAX(BR.dStart) <= @dDate

RETURN @result

• When supplied the argument 06-06-2008, it correctly returns 600.
• When supplied the argument 03-03-2009, it correctly returns 800
• When supplied the argument 02-02-2010, it should return 700. The function actually returns 800.

Where is the bug?

bug hunting: If I tweak around with the figures, it seems to pick the largest rate if it has 2 values to pick from.

Answer 1

You should select top 1 using TOP 1 get the appropriate one with ORDER BY

DECLARE @result decimal(38,20)

SELECT TOP 1 @result = decRate
FROM BudgetRates BR
WHERE BR.iRefEmployeeID = @iEmployeeID
ORDER BY decRate DESC

RETURN @result

Answer 2

Surely your code shouldn't be grouping at all?

SELECT TOP 1 @result = decRate
FROM BudgetRates BR
WHERE BR.iRefEmployeeID = @iEmployeeID
AND BR.dStart <= @dDate
ORDER BY BR.dStart DESC

RETURN @result

Answer 3

try this mate:

select @result = decRate
    from BudgetRates
      inner join (
       select max(BR.dStart) as MaxDate
       from BudgetRates
      where BR.dStart <= @dDate
        and BR.iRefEmployeeID = @iEmployeeID
                 ) temp on tmep.MaxDate = BudgetRates.dStart
                   and BudgetRates.iRefEmployeeID = @iEmployeeID

Answer 4

Seems like you are supposed to use ranking functions here.

DECLARE @result decimal(38,20)

SELECT @result = decRate 
(
  SELECT decRate, ROW_NUMBER() OVER (ORDER BY BR.dStart DESC) rownum
  FROM BudgetRates BR
  WHERE BR.iRefEmployeeID = @iEmployeeID
  AND BR.dStart <= @dDate
) sub
WHERE rownum = 1

RETURN @result