December 11, 2017 at 1:53 pm
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
December 11, 2017 at 2:07 pm
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.
December 11, 2017 at 2:33 pm
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
December 11, 2017 at 2:40 pm
Yes, works perfectly. Thank you!
December 16, 2017 at 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
December 16, 2017 at 7:22 am
texpic - Saturday, December 16, 2017 4:49 AMIf 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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply