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.

bughunting: Now if I "ruin" my data and edit the 700 rate to 810. It correctly picks the that rate when the question is 02-02-2010. — DoStuffZ
– DoStuffZ, Commented Jul 14, 2010 at 13:58
Ahh there was something. Solution giving the correct answer: DECLARE @result decimal(38,20) SELECT TOP 1 @result = decRateWeekday FROM tbl_eCon_Target_BudgetRates BR WHERE BR.iRefEmployeeID = @iEmployeeID GROUP BY decRateWeekday, dStart HAVING MAX(BR.dStart) <= @dDate ORDER BY dStart DESC RETURN @result thank you so much for the help, I really appreciate it. — DoStuffZ
– DoStuffZ, Commented Jul 14, 2010 at 14:02

fritterfatboy · Accepted Answer · 2010-07-14 13:44:34Z

4

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

answered Jul 14, 2010 at 13:44

fritterfatboy

3372 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DoStuffZ Over a year ago

When I leave out the GROUP BY I get: Column 'BudgetRates.decRate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. And Top 1 picks the rate 600.

Martin Smith Over a year ago

@DoStuffz - You can't have left out the GROUP BY if you get that message.

Martin B Over a year ago

@DoStuffz: If TOP 1 is picking a rate of 600, that sounds as if you left off the DESC on the ORDER BY.

Raj More · Accepted Answer · 2010-07-14 14:04:12Z

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

edited Jul 14, 2010 at 14:04

answered Jul 14, 2010 at 13:43

Raj More

48.2k34 gold badges139 silver badges199 bronze badges

2 Comments

DoStuffZ Over a year ago

When I do this I get. "Invalid use of a side-effecting operator 'SET ROW COUNT' within a function."

Raj More Over a year ago

oh.. i forgot you can't do that in a function.. correcting for Top 1

ercan · Accepted Answer · 2010-07-14 14:07:49Z

1

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

answered Jul 14, 2010 at 14:07

ercan

1,7171 gold badge20 silver badges34 bronze badges

Comments

meeron · Accepted Answer · 2010-07-14 14:07:48Z

0

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

answered Jul 14, 2010 at 14:07

meeron

761 bronze badge

Collectives™ on Stack Overflow

SQL function returns wrong value

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related