# Find rows divisible by amount

### Question

• I want to be able to find (and update) rows in a table that have an amount exactly divisible by another amount, kind of like the MOD function in Visual Basic.  For example, if the column [Amount] in the table below is divisible by 1.75 and has all zeros to right of the decimal in the result then do something, else do something else.  So, if the [Amount] value was 640.00 it would return false because 640.00 / 1.75 = 365.7142857142857 but if the [Amount] value was 175.00 it would return true.  Thanks.

[GLID] [int] NULL,
[DebitAccountID] [int] NULL,
[CreditAccountID] [int] NULL,
[Amount] [money] NOT NULL,
[Notes] [varchar](max) NULL,
PRIMARY KEY CLUSTERED
(
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Monday, April 29, 2013 3:08 PM

• ```select *
where Amount/1.75 = floor(Amount/1.75)```

Floor

David

David http://blogs.msdn.com/b/dbrowne/

• Proposed as answer by Monday, April 29, 2013 3:28 PM
• Marked as answer by Monday, April 29, 2013 3:30 PM
Monday, April 29, 2013 3:24 PM
• Hi -

Would this work?

```Declare @GLAdjustment Table
( GLAdjustmentID int, GLID int , DebitAccountID int
,CreditAccountID int, Amount money, Notes varchar(max)
)
(GLAdjustmentID , GLID, DebitAccountID, CreditAccountID , Amount , Notes)
Values (1, 101, 120, 130, 175.00, 'Some Notes')
, (2, 102, 120, 130, 200.00, 'Some Notes')
, (3, 205, 120, 130, 35.00, 'Some Notes')
, (4, 908, 120, 130, 170.00, 'Some Notes')
, (5, 908, 120, 130, 640.00, 'Some Notes')

/** return all rows **/
/** return all rows that are not divisible by 1.75
, using the sql mod operator (%)
**/
where g.Amount%1.75 <> 0```

- will

• Marked as answer by Monday, April 29, 2013 3:31 PM
Monday, April 29, 2013 3:30 PM

### All replies

• ```select *
where Amount/1.75 = floor(Amount/1.75)```

Floor

David

David http://blogs.msdn.com/b/dbrowne/

• Proposed as answer by Monday, April 29, 2013 3:28 PM
• Marked as answer by Monday, April 29, 2013 3:30 PM
Monday, April 29, 2013 3:24 PM
• Hi,

Not sure, whether you need something like this..

```select case when (640.00 / 1.75)> convert(int,(640.00 / 1.75)) then 'Greater'
when (640.00 / 1.75)> convert(int,(640.00 / 1.75)) then 'equal'
end
```

Regards,

Brindha.

Monday, April 29, 2013 3:26 PM
• Hi -

Would this work?

```Declare @GLAdjustment Table
( GLAdjustmentID int, GLID int , DebitAccountID int
,CreditAccountID int, Amount money, Notes varchar(max)
)
(GLAdjustmentID , GLID, DebitAccountID, CreditAccountID , Amount , Notes)
Values (1, 101, 120, 130, 175.00, 'Some Notes')
, (2, 102, 120, 130, 200.00, 'Some Notes')
, (3, 205, 120, 130, 35.00, 'Some Notes')
, (4, 908, 120, 130, 170.00, 'Some Notes')
, (5, 908, 120, 130, 640.00, 'Some Notes')

/** return all rows **/
/** return all rows that are not divisible by 1.75
, using the sql mod operator (%)
**/
where g.Amount%1.75 <> 0```

- will

• Marked as answer by Monday, April 29, 2013 3:31 PM
Monday, April 29, 2013 3:30 PM
• Do not ever use MONEY! It is leftover from 1970's Sybase and it does not do correct math. It rounds incorrectly in multiplication and division. It is also illegal in some cases because of precision. Make this a DECIMAL() data type instead.

I also see that you do not know what a key is; why would you use the physical count of insertion attempts to a particular hard disk? Shouldn't this be a Journal entry?

Why are you using integers in your Chart of Accounts? I have never seen that; since accounts are tag numbers, they are strings in a hierarchical encoding scheme. The only reason to use INTEGER is to do math on them, so what computations are you doing?

(journal_entry_nbr CHAR(10) NOT NULL PRIMARY KEY,
debit_account_id CHAR(10)NOT NULL,
credit_account_id CHAR(10) NOT NULL,
adjustment_amt DECIMAL (20,5) NOT NULL, -- talk to accounting dept

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Monday, April 29, 2013 3:32 PM
• I completely forgot about the % (MOD) operator. Thanks.
Monday, April 29, 2013 4:00 PM
• Thanks for the heads up on the money data type.  This isn't our design but your ideas will help in our future designs.
Monday, April 29, 2013 4:01 PM