Check if value is whole value only

Transact-SQL
1

CREATE TABLE [dbo].[HOURS](
[EMPL_ID] nchar NULL,
[ENTERED_HRS] [decimal](14, 2) NULL
) ON [PRIMARY]
GO

INSERT INTO HOURS (EMPL_ID, ENTERED_HRS, CHECK_HRS)
SELECT 'TRACEY',10.00

INSERT INTO HOURS (EMPL_ID, ENTERED_HRS, CHECK_HRS)
SELECT 'SAM',40.00

SELECT *,
ROUND(A.ENTERED_HRS,0)/8 AS CHECK_HRS

FROM HOURS A

Returns the following
|TRACEY |10.00|1.250000|
|SAM |40.00|5.000000|

Question - I only want to return the rows if the value is a whole number i.e no remainder amount.
The only record valid here is SAM

2

It would be nice if you tested your sample data and insert statements. You can use modulo. Add this to the end of your statement

where ROUND(A.ENTERED_HRS,0)/8 % 1 = 0

1 Like
3

thanks mike

another way .. could be very expensive .. but anyway

where FLOOR(CHECK_HRS) = CEILING(CHECK_HRS)

use what
go

CREATE TABLE [dbo].[HOURS](
[EMPL_ID] nchar(100) NULL,
[ENTERED_HRS] [decimal](14, 2) NULL
) ON [PRIMARY]
GO

INSERT INTO HOURS (EMPL_ID, ENTERED_HRS)
SELECT 'TRACEY',10.00

INSERT INTO HOURS (EMPL_ID, ENTERED_HRS)
SELECT 'SAM',40.00

; with cte as 
(
SELECT *,
ROUND(A.ENTERED_HRS,0)/8 AS CHECK_HRS
FROM HOURS A
)
select * from cte where FLOOR(CHECK_HRS) = CEILING(CHECK_HRS)

drop table Hours
4

I didn't know about this % how interesting

5

Let me look at the FLOOR and CEILING functions as well.

Thank you both

6

It's a function called "Modulus" or "Modulo" and is available in most languages. Basically, it's the mathematical operation that does division and produces the remainder of the division. It can be used to do a fair bit of mathematical magic regardless of which language you're using.

Here's the basic documentation for the operator in SQL Server.

Unfortunately, the examples given are only "syntactical" examples. The operator has a fair number of powerful uses (especially to easily generate millions of rows of test data) and the one use (is there are remainder, which is also asking if it's a whole number) Mike used it for is another common and very effective usage.

2 Likes