April 10, 2019 at 3:22 pm
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
April 10, 2019 at 3:34 pm
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
April 10, 2019 at 3:40 pm
Thanks @Taps! That worked!!
April 10, 2019 at 9:51 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply