Home Forums SQL Server 2012 SQL Server 2012 - T-SQL complicated sql syntax - looking for explanation for working query RE: complicated sql syntax - looking for explanation for working query

  • May 5, 2014 at 5:55 pm

    #1710904

    KoldCoffee (5/5/2014)

    At this point, the recurrent question lurking in my mind is 'why do we have to count from zero' instead of '1' referring to 'SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1

    It's just simple math. Since we want to use all the values that ROW_NUMBER() produces by adding them to the start date/time to come up with a date/time incremented by minute, the first value has to be "0" so that when you add the value of ROW_NUMBER() to the start date/time, the first value (the one with the "0") will return the start date/time as expected. Since the first value (1) was shifted towards "0" by 1, all the values must be shifted towards "0" by 1 or you'll end up with a gap. Subtracting 1 effectively moves the value that ROW_NUMBER() produced towards "0" by 1.

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