Calculating if number has gone up a level

[Jhankins]

I have two columns and need to compare if the new sales value has increased to the next level.. example will explain better:

Current Value Max Value Achieved
2400 2600 since $2K has been hit dnt count

3210 2500 Count

10500 11200 Count

I know if you do left(Current Value,1)> Left(Max,1) this will calc when it is a single digit but dont know how to work out if 2 digits is need to see if it has increased..

 

[vongrunt]

This looks cryptic... can you post some more sample data + expected results?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Jhankins]

I am just trying to see if the current sales value has moved up a level (levels are at $1000 intervals) ..
say.

data example
id Name Current Sales Max Past Sales
1 bill $2333 $2400
2 Jan $3400 $2400
3 Fred $11250 $10123

I would want to return the 2 & 3 as they both have had a new sales record and have increased to the next $1000 bracket.. Hope this explains more..

Current Past High
$2333 $2400 -this wouldn't count as it hasn't hit a new record

$3400 $2400 - this would come up as it has increased enough

$11250 $10123 - this would count....

 

[vongrunt]

Try this:

select *
from myTable
where convert(int, currentSales/1000) = 1 + convert(int, maxPastSales/1000)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[hmckillop]

i am missing something?

select * from myTable
WHERE currentsales - maxpastsales >=1000

"I'm living so far beyond my income that we may almost be said to be living apart

 

[vongrunt]

Level go 1000, 2000, 3000... not 1000 greater than previous value.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[hmckillop]

I knew I was missing something!

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Jhankins]

Thanks for all your help.. and yes it worked...

Cheers to you Vongrunt