How can I null out all but the most recent records in this table?

  • Goalie35

    Say Hey Kid

    Points: 696

    I have an old table that contains a sort of history tracking of clients & their info that we've imported.  I now need to null out a field named "RecordType" of all prior records for each client EXCEPT the most recent record for that client, but I'm not sure how to do this.

    In the below example, I have 3 clients.  We import via batches, so in this example, we know that the most recent import was BatchId = 3.  The problem is, not all clients are in each batch, so we need to find the most recent batch they were in, then null out the RecordType of any records that came before that batch.  Here's the example:

      CREATE TABLE #tmpClientImports
    (
    ClientImportsId INT,
    ClientId INT,
    ClientName VARCHAR(50),
    RecordType CHAR(1),
    BatchId INT
    )

    INSERT INTO #tmpClientImports Select 1, 1, 'ABC Company', 'C', 1
    INSERT INTO #tmpClientImports Select 2, 20, 'Sprockets Inc', 'C', 1
    INSERT INTO #tmpClientImports Select 3, 30, 'Vandalay Industries', 'U', 1
    INSERT INTO #tmpClientImports Select 4, 20, 'Sprockets Inc', 'U', 2
    INSERT INTO #tmpClientImports Select 5, 30, 'Vandalay Industries', 'A', 2
    INSERT INTO #tmpClientImports Select 6, 20, 'Sprockets Inc', 'A', 3

    SELECT * FROM #tmpClientImports
    DROP TABLE #tmpClientImports

    So, in this example, the output I would want would be the following.  ABC Company's most recent batch is 1, Vandalay Industries is 2, so we null out the RecordType from their 1st batchID, and Sprockets Inc's last one is BatchID 3, so we null out all of their prior batches.  This then gives us only the most recent record that hasn't been nulled out:

    ClientImportsId      ClientId        ClientName                 RecordType           BatchId

    1                                         1                      ABC Company                C                                    1

    2                                       20                     Sprockets Inc                 NULL                            1

    3                                       30                     Vandalay Industries     NULL                            1

    4                                       20                     Sprockets Inc                 NULL                            2

    5                                       30                     Vandalay Industries     A                                     2

    6                                       20                     Sprockets Inc                 A                                     3

     

    Thanks

  • @Taps

    SSC Eights!

    Points: 841

    Does this work for you

     ;WITH cte_test 

    AS ( SELECT *
    , ROW_NUMBER() OVER (PARTITION BY clientid ORDER BY ClientImportsId desc ) AS ranking
    FROM #tmpClientImports
    --ORDER BY ClientName
    )
    UPDATE c
    SET c.RecordType = NULL
    FROM cte_test c
    WHERE c.ranking <> 1
  • Goalie35

    Say Hey Kid

    Points: 696

    Thanks @Taps!  That worked!!

  • drew.allen

    SSC Guru

    Points: 76658

    @Taps wrote:

    Does this work for you

     ;WITH cte_test 

    AS ( SELECT *
    , ROW_NUMBER() OVER (PARTITION BY clientid ORDER BY ClientImportsId desc ) AS ranking
    FROM #tmpClientImports
    --ORDER BY ClientName
    )
    UPDATE c
    SET c.RecordType = NULL
    FROM cte_test c
    WHERE c.ranking <> 1

    ROW_NUMBER() only ever produces positive integers.  There is absolutely no reason to test for 0 or negative integers.

    WHERE c.ranking > 1 is sufficient.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 4 posts - 1 through 4 (of 4 total)

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