Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Get list of all workers having same SET of Rates RE: Get list of all workers having same SET of Rates

  • April 8, 2014 at 7:33 am

    #1704131

    a.myasnikov (4/8/2014)

    Just wondering what the grand-masters will say about a checksum solution?

    From what I know about checksums they are pretty reliable way to tell one set from the other... Any comments are welcome.

    ; WITH CTE

    as (

    SELECT worker_id,

    CHECKSUM_AGG(CHECKSUM(rate_code)) chk

    FROM #worker_rate

    GROUP BY worker_id

    )

    SELECT worker_id,

    DENSE_RANK() OVER (ORDER BY chk) SNO

    FROM CTE

    That would be a great idea and it definitely shows "thinking outside the box" but... CHECKSUM and CHECKSUM_AGG use a simple "Exlusive OR" (an "adder" by any other name) that allows for duplicates to occur.

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