none
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.

    CREATE TABLE [dbo].[GLAdjustment](
     [GLAdjustmentID] [int] IDENTITY(1,1) NOT NULL,
     [GLID] [int] NULL,
     [DebitAccountID] [int] NULL,
     [CreditAccountID] [int] NULL,
     [Amount] [money] NOT NULL,
     [Notes] [varchar](max) NULL,
    PRIMARY KEY CLUSTERED
    (
     [GLAdjustmentID] ASC
    )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

Answers

  • select * 
    from GLAdjustment
    where Amount/1.75 = floor(Amount/1.75)

    Floor

    David


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

    • Proposed as answer by RSingh() Monday, April 29, 2013 3:28 PM
    • Marked as answer by dlchase89 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) 
      )
    Insert Into @GLAdjustment 
    	(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 **/
    select * from @GLAdjustment g
    /** return all rows that are not divisible by 1.75
       , using the sql mod operator (%)
    **/  
    select * from @GLAdjustment g
    where g.Amount%1.75 <> 0


    - will

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

All replies

  • select * 
    from GLAdjustment
    where Amount/1.75 = floor(Amount/1.75)

    Floor

    David


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

    • Proposed as answer by RSingh() Monday, April 29, 2013 3:28 PM
    • Marked as answer by dlchase89 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) 
      )
    Insert Into @GLAdjustment 
    	(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 **/
    select * from @GLAdjustment g
    /** return all rows that are not divisible by 1.75
       , using the sql mod operator (%)
    **/  
    select * from @GLAdjustment g
    where g.Amount%1.75 <> 0


    - will

    • Marked as answer by dlchase89 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? 

    CREATE TABLE GL_Adjustments
    (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
     adjustment_notes VARCHAR(200));

    --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