Populate Acct Number (based on record above)

• texpic

  SSCertifiable

  Points: 5887

  More actions

  December 11, 2017 at 1:53 pm

  #326930

  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: 183706

  More actions

  December 11, 2017 at 2:07 pm

  #1971665

  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: 77006

  More actions

  December 11, 2017 at 2:33 pm

  #1971668

  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: 5887

  More actions

  December 11, 2017 at 2:40 pm

  #1971670

  Yes, works perfectly.  Thank you!

• texpic

  SSCertifiable

  Points: 5887

  More actions

  December 16, 2017 at 4:49 am

  #1972375

  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: 51550

  More actions

  December 16, 2017 at 7:22 am

  #1972379

  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