Next to last Min Value 1

[seyfert]

Greetings,

I am calculating a % change in Salary over a year. I use the Min(Regular Pay) and the Max(Regular Pay). However, If a new employee starts, .01 cents is deposited into their checking account to make sure direct deposit goes through.

The Min(Regular Pay), in this case, shows up as .01; thereby, throwing off the % change in Salary BIG time.

I'm thinking of a CASE WHEN Min(Regular Pay) <> .01 THEN.....I don't know what to put after the THEN.

Can anyone help me out?

Thanks and have a great weekend,
Wendy

 

[TonyGroves]

You could use something like:
[tt]
SELECT
MIN(IF(regularpay=0.01,NULL,regularpay)) minpay
FROM ...
[/tt]
You could also use CASE instead of IF.

 

[seyfert]

Thanks for your suggestion Tony, but it' not working. I'm getting a datatype mismatch.

I don't know if I need to change the value of the .01 and then put it in a loop to run through the line again.

Or maybe use an index, but I have no idea how I could do that.

 

[TonyGroves]

No, it wouldn't work, sorry about that. The problem is that the field is a FLOAT (am I right?), and when you store 0.01 in a float field it probably ends up as something like 0.999999999564, which does not equal 0.01.

You could use instead:[tt]
IF(regularpay<0.02 ...[/tt]

If that's not the solution, then please let us know exactly what errors you are getting.

 

[TonyGroves]

Sorry, that value should have been 0.00999999999564.

 

[seyfert]

I'm not sure how the field is set up. We get the file from another agency.

I am using Discoverer and pulling MIN(Regular Pay) from the 'Select Items' tab. If I write a condition: MIN(Regular Pay) <> .01, I don't get any info back associated with that person. I don't know if I explained that so you understand what is going on.

I did find that if I calculate the MIN(Regular Pay) on the 'Calculations' tab, then create a condition where Regular Pay <> .01, I get the next lowest value.

I'm not sure why that works and I need to look at the data to make sure it is correct, but at first glance, it seems to work.

Thanks for all your help.

Wendy