Post reply

The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

  • November 16, 2019 at 12:23 pm

    #3698257

    Fantastic article, do you have any links to using the %  to return value. never seen the use of % like this (T.N-1)%3 +1

    thanks

     

    ***The first step is always the hardest *******

  • November 16, 2019 at 12:51 pm

    #3698262

    SGT_squeequal wrote:

    Fantastic article, do you have any links to using the %  to return value. never seen the use of % like this (T.N-1)%3 +1

    thanks

    The % is the modulus operator, returns the remainder of the division of the value by the second parameter.

    😎

     

  • November 16, 2019 at 10:19 pm

    #3698303

    SGT_squeequal wrote:

    Fantastic article, do you have any links to using the %  to return value. never seen the use of % like this (T.N-1)%3 +1

    Thank you for the feedback... much appreciated.

    As Eirikur states, the "%" (in this case) is the arithmetic "modulo" operator.  In SQL Server, that means the straight remainder of  division (and it works equally well with integers and real numbers, both positive and negative).  It's not quite the same as the modulo operator that EXCEL and the Scientific Calculator in Windows use because those use the "FLOOR" method for calculating Modulo rather than the TRUNCATE method that SQL Server (and most humans) use.

    For example, -5%3 will return a "-2" and MOD(-5,3) in Excel will return a "1".  Both answers are technically and mathematically correct.  You just have to know which method of calculation is being used behind the scenes.  To wit, a whole lot of programming languages use the more human natural truncate method (also known as the "Remainder" or "Remainder Method") while others use the not so human natural floor method (described as the"least positive residue" or "modular arithmetic" method in which remainders are only positive) and the documentation will usually NOT tell you which one is being used.  The -5 MOD 3 test is a good test for this.

    Here's a link for the "% (modulo)" arithmetic operator in SQL Server

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/modulo-transact-sql?view=sql-server-ver15

    WikiPedia has a good introduction to the variants of the operation as well as links to other articles on the subject.

    https://en.wikipedia.org/wiki/Modulo_operation

    Our human base 10 numbering system is based on "Mod 10"... it only has digits for 0 to 9.

    Also, there's a functional substitution for the Tally Table.  One example of that can be found at the "fnTally" link in my signature line below.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 3 posts - 496 through 497 (of 497 total)

You must be logged in to reply to this topic. Login to reply