Check numeric value for whole and certain increment

[French79]

How can I do a data integrity check on a numeric field. I need to check to make sure that the data is a whole number and if the number is in decimal format, that the decimal is either .25, .5(0) and .75. I need to pull the rows where the data is not whole or the decimal that I indicated.

Example

VacationAccrual
1
2
3.25
4.56
5.5
6
6
7.78

I need to list 4.56 and 7.78 as the invalid records

Thanks in advance.

 

[gmmastros]

Multiply by 4 and then check for whole number.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

I did not put this in a table but you should be able to get the idea.

declare @test as decimal(9,2)
set @test = 3.65
select @test, cast(@test as int), @test-cast(@test as int) as Remainder
where @test-cast(@test as int) not in (.25, .5, .75)

Simi

 

[gmmastros]

simian336,

There's an easier way. SQL Server has a mod function. With SQL2000, it didn't work with numerics, but with SQL2005+, it works just fine.

Declare @Temp Table(Data Decimal(10,2))

Insert Into @Temp Values(1)
Insert Into @Temp Values(2)
Insert Into @Temp Values(3.25)
Insert Into @Temp Values(4.56)
Insert Into @Temp Values(5.5)
Insert Into @Temp Values(6)
Insert Into @Temp Values(6)
Insert Into @Temp Values(7.78)

Select *
From   @Temp
Where  Data % 0.25 > 0

If you try to run this on a SQL2000 database, you'll get the following error:

[red]Operand data type numeric is invalid for modulo operator.[/red]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Good to know George...

Thanks

Simi