Populate Acct Number (based on record above)

  • texpic

    SSCertifiable

    Points: 5882

    I have a file I get every month that has the account number on one line and the detail is above it.  When I read it into SQL I add an identity field (that is reason for sort DESC on ID).  How can I populate the MyAcctNumber field so the final table has the result that the update statement produces.  There are 100,000 or so records; generally 2 to 5 blank lines.  Thanks.


    CREATE TABLE #Test
    (Id int, MyAcctNumber varchar(20), MyName varchar(20), Amount int)
    INSERT INTO #Test
    SELECT 10, '001', 'AAA', 100 UNION ALL
    SELECT 9, '', '', 200 UNION ALL
    SELECT 8, '', '', 300 UNION ALL
    SELECT 7, '002', 'BBB', 199 UNION ALL
    SELECT 6, '', '', 299 UNION ALL
    SELECT 5, '', '', 399 UNION ALL
    SELECT 4, '', '', 499 UNION ALL
    SELECT 3, '003', 'CCC', 777 UNION ALL
    SELECT 2, '004', 'DDD', 100 UNION ALL
    SELECT 1, '', '', 200

    --before
    SELECT * FROM #Test ORDER BY Id DESC

    --need to do "this", about 100,000 records in real file
    UPDATE #Test SET MyAcctNumber = '001' WHERE Id = 9
    UPDATE #Test SET MyAcctNumber = '001' WHERE Id = 8
    UPDATE #Test SET MyAcctNumber = '002' WHERE Id = 6
    UPDATE #Test SET MyAcctNumber = '002' WHERE Id = 5
    UPDATE #Test SET MyAcctNumber = '002' WHERE Id = 4
    UPDATE #Test SET MyAcctNumber = '004' WHERE Id = 1

    --after
    SELECT * FROM #Test ORDER BY Id DESC

  • Luis Cazares

    SSC Guru

    Points: 183633

    Maybe something like this?

    UPDATE t SET
      MyAcctNumber = x.MyAcctNumber
    FROM #Test t
    JOIN (
      SELECT Id, LAG( Id,1,0) OVER( ORDER BY Id) LAGId, MyAcctNumber
      FROM #Test
      WHERE MyAcctNumber <> '') x ON t.Id > x.LAGId AND t.Id < x.Id;

    I'm not sure on how would this perform.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • drew.allen

    SSC Guru

    Points: 76735

    This version only needs to read the table once.

    ;
    WITH Data_Smear AS
    (
        SELECT *,
            STUFF(MIN(CAST(Id AS BINARY(5)) + CAST(NULLIF(MyAcctNumber, '') AS BINARY(20))) OVER(ORDER BY Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 1, 5, NULL) AS NewAcctNumber
        FROM #Test2
    )
    UPDATE Data_Smear
    SET MyAcctNumber = NewAcctNumber
    ;

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • texpic

    SSCertifiable

    Points: 5882

    Yes, works perfectly.  Thank you!

  • texpic

    SSCertifiable

    Points: 5882

    If the ID was ascending (original example was desc)  how would I do this?  I tried modifying the examples but couldn't get it to work.  Also if there were more than 2 to 5 blank lines; sometimes 100 would this make a difference.  Thanks.


    CREATE TABLE #Test
    (Id int, MyAcctNumber varchar(20), MyName varchar(20), Amount int)
    INSERT INTO #Test
    SELECT 1, '001', 'AAA', 100 UNION ALL
    SELECT 2, '', '', 200 UNION ALL
    SELECT 3, '', '', 300 UNION ALL
    SELECT 4, '002', 'BBB', 199 UNION ALL
    SELECT 5, '', '', 299 UNION ALL
    SELECT 6, '', '', 399 UNION ALL
    SELECT 7, '', '', 499 UNION ALL
    SELECT 8, '003', 'CCC', 777 UNION ALL
    SELECT 9, '004', 'DDD', 100 UNION ALL
    SELECT 10, '', '', 200

  • J Livingston SQL

    SSC Guru

    Points: 51272

    texpic - Saturday, December 16, 2017 4:49 AM

    If the ID was ascending (original example was desc)  how would I do this?  I tried modifying the examples but couldn't get it to work.  Also if there were more than 2 to 5 blank lines; sometimes 100 would this make a difference.  Thanks.


    CREATE TABLE #Test
    (Id int, MyAcctNumber varchar(20), MyName varchar(20), Amount int)
    INSERT INTO #Test
    SELECT 1, '001', 'AAA', 100 UNION ALL
    SELECT 2, '', '', 200 UNION ALL
    SELECT 3, '', '', 300 UNION ALL
    SELECT 4, '002', 'BBB', 199 UNION ALL
    SELECT 5, '', '', 299 UNION ALL
    SELECT 6, '', '', 399 UNION ALL
    SELECT 7, '', '', 499 UNION ALL
    SELECT 8, '003', 'CCC', 777 UNION ALL
    SELECT 9, '004', 'DDD', 100 UNION ALL
    SELECT 10, '', '', 200

    SELECT *,
       STUFF(MAX(CAST(Id AS BINARY(5)) + CAST(NULLIF(MyAcctNumber, '') AS BINARY(20))) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ), 1, 5, NULL) AS NewAcctNumber
      FROM #Test

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

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

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