December 20, 2015 at 1:23 pm
Hello All!!
Greetings and Happy holidays!
I am a newbie in SQL and I am trying to solve a problem. I googled but could not find the solution. If this is in a wrong forum, I request you to move it to appropriate forum.
Thanks in advance for any insights you may give me! Appreciate it!
My Table:
[font="Courier New"]
ID1 ID2 RECNBR ACTFLG
ABDCDDFDD 1 1 0
JHKLLLKHJ 9 1 0
SDFKJLSDK 4 1 0
LKJHJNHGJ 9 2 1 <----- Error (only the record with highest RECNBR for a particular ID2 can have ACTFLG as 1
SDFDSLLSD 2 1 0
BDFDFDFDD 1 2 1
DSFKLKJSD 4 2 1
LOKKJLLLH 9 3 1
GFLDSFFGL 2 2 1[/font]
Few Facts:
1. ID1 is an unique Alphanumeric column which is unique for each record.
2. ID2 Numeric values but not necessarily in sorted order.
3. RECNBR - Starts with 1 for each ID2 and increments by 1 for each new record for the same ID2.
4. ACTFLG - This field is set the '1' for the highest RECNBR within the same ID2. All other records should have zero.
5. Records are not in any sorted order.
Issue:
1. If you notice the ID2 '9'. It has 3 records with RECNUM 1, 2 and 3. As per facts only RECNUM '3' can have a ACTFLG of '1'. However in my table, I have both RECNUM '2' and also '3' with ACTFLG '1'.
Requirement:
Note: I am trying to avoid loops. I have a solution using loops and it is taking lot of time to execute. I am trying to see if I can do this with some simple 1 or 2 line code using system functions such as PARTITION/GROUP BY etc.
I can set ACTFLG to '0' for all records. I need coding ideas on how to identify the record with highest RECNUM with in a particular ID2. Once identified I want to update the ACTFLG to '1' for those records.
OR
Identify records such as ID2 '9', RECNUM '2' (which has issue) and update the ACTFLG to '0'.
December 20, 2015 at 7:05 pm
Excellent description in your post. The only thing that was missing was actual readily consumable data. I've included that in my response but see the first link under "Helpful Links" in my signature lines below for more details.
As to your problem, details are in the code below. The comments should satisfactorily suffonsify your sufficiency for knowledge superbly and saying any more would be superfluous. 😀
--========================================================================================
-- Create a test table and populate it.
-- Nothing in this section is a part of the solution. Just creating test data.
--========================================================================================
--===== If the test table already exists, drop it to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create a test table to demo with and populate it on the fly
SELECT *
INTO #TestTable
FROM (
SELECT 'ABDCDDFDD',1,1,0 UNION ALL
SELECT 'JHKLLLKHJ',9,1,0 UNION ALL
SELECT 'SDFKJLSDK',4,1,0 UNION ALL
SELECT 'LKJHJNHGJ',9,2,1 UNION ALL --<----- Original Error
SELECT 'SDFDSLLSD',2,1,0 UNION ALL
SELECT 'BDFDFDFDD',1,2,1 UNION ALL
SELECT 'DSFKLKJSD',4,2,0 UNION ALL --<----- Added another here to demonstrate
SELECT 'LOKKJLLLH',9,3,1 UNION ALL
SELECT 'GFLDSFFGL',2,2,1
) d (ID1,ID2,RECNBR,ACTFLG)
;
--========================================================================================
-- Demonstrate the "Find, Fix, and Verify"
-- It uses the special "trick" of updating the underlying table of the CTE by
-- updating the CTE itself.
--========================================================================================
--===== Demonstrate how to just find the errors using a CTE.
-- You don't actually need this but it's verification that the two errors do exist.
-- You'll also notice that the update coming up is almost identical.
WITH
cteEnumerate AS
(
SELECT ID2RowNum = ROW_NUMBER() OVER (PARTITION BY ID2 ORDER BY RECNBR DESC)
, *
FROM #TestTable
)
SELECT *
FROM cteEnumerate
WHERE (ID2RowNum > 1 AND ACTFLG = 1)
OR (ID2RowNum = 1 AND ACTFLG = 0)
;
--===== Demonstrate how to update them THROUGH the CTE.
-- This actually finds and fixes the two rows. Note the differences between this
-- and the previous snippet of code. Not many changes.
WITH
cteEnumerate AS
(
SELECT ID2RowNum = ROW_NUMBER() OVER (PARTITION BY ID2 ORDER BY RECNBR DESC)
, ACTFLG
FROM #TestTable
)
UPDATE cteEnumerate
SET ACTFLG = CASE WHEN ID2RowNum = 1 THEN 1 ELSE 0 END
WHERE (ID2RowNum > 1 AND ACTFLG = 1)
OR (ID2RowNum = 1 AND ACTFLG = 0)
;
--===== Same "Find" code as we started with.
-- This time, all errors have been repaired and no rows are returned.
WITH
cteEnumerate AS
(
SELECT ID2RowNum = ROW_NUMBER() OVER (PARTITION BY ID2 ORDER BY RECNBR DESC)
, *
FROM #TestTable
)
SELECT *
FROM cteEnumerate
WHERE (ID2RowNum > 1 AND ACTFLG = 1)
OR (ID2RowNum = 1 AND ACTFLG = 0)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 10:58 pm
Thank you very much for your help! It helped!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply