April 16, 2010 at 11:27 pm
I have an issue where I need to 'Flag' based on a test value between 1 and -1. My data is in date order, where I need find the first 1 and then after this record the first -1 to be a grouping Flagging the range...and so on down the data. Also, if I have no additional -1 but I have hit a 1 in my set I need to continue Flag to end of record set.
I have some 0s and NULLs in my Test field that I'm not concerned with for this Flag set but it's in my src data so noting it.
Below is a very small example of data:
-- Test data
DECLARE @TestTable TABLE (Id INT, Date DATETIME, Test INT, Flag INT)
INSERT INTO @TestTable
SELECT 11,'2010-03-01',-1,NULL UNION
SELECT 12,'2010-03-02',NULL,NULL UNION
SELECT 13,'2010-03-03',0,NULL UNION
SELECT 14,'2010-03-04',1,NULL UNION
SELECT 15,'2010-03-05',NULL,NULL UNION
SELECT 16,'2010-03-06',NULL,NULL UNION
SELECT 17,'2010-03-07',1,NULL UNION
SELECT 18,'2010-03-08',NULL,NULL UNION
SELECT 19,'2010-03-09',-1,NULL UNION
SELECT 20,'2010-03-10',0,NULL UNION
SELECT 21,'2010-03-11',-1,NULL UNION
SELECT 22,'2010-03-12',1,NULL UNION
SELECT 23,'2010-03-13',NULL,NULL UNION
SELECT 24,'2010-03-14',NULL,NULL
SELECT * FROM @TestTable ORDER BY 1
My expected Result set would be:
112010-03-01 -1NULL
122010-03-02 NULLNULL
132010-03-03 0NULL
142010-03-04 11
152010-03-05 NULL1
162010-03-06 NULL1
172010-03-07 11
182010-03-08 NULL1
192010-03-09 -11
202010-03-10 0NULL
212010-03-11 -1NULL
222010-03-12 11
232010-03-13 NULL1
242010-03-14 NULL1
Thank you for any help you can lend in resolving this. Love this site and have used for reference for years but this is my first posting .. please help. The only way I can figure at this point to do this is walk the data with a cursor and with over 11 million records I want to stay way from that.
April 17, 2010 at 12:28 pm
I don't like it as it's slow as can be looping 11+ Million records but this does what I need... if anyone can give me suggestions how to do this better??
DECLARE @Id INT, @test-2 INT, @Flag INT
DECLARE tCURSOR CURSOR FAST_FORWARD
FOR SELECT Id,Test FROM @TestTable ORDER BY Id
OPEN tCURSOR
FETCH NEXT FROM tCURSORINTO @Id, @test-2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Flag = (
CASE WHEN @Flag = 1 THEN
@Flag /* When Flag is True always Next one as True */
ELSE
CASE WHEN @test-2 = 1 THEN
1 /* New Flag */
ELSE
NULL
END
END
)
UPDATE @TestTable
SET Flag = @Flag
WHERE Id = @Id
SET @Flag = (CASE WHEN @Flag = 1 AND @test-2 = -1 THEN NULL ELSE @Flag END ) /* Clear Flag when Hit -1 */
FETCH NEXT FROM tCURSORINTO @Id, @test-2
END
CLOSE tCURSOR
DEALLOCATE tCURSOR;
SELECT * FROM @TestTable ORDER BY 1
April 17, 2010 at 1:39 pm
Seems like the requirement calls for the quirky update (see Jeffs great article [/url] for details.
Side note: I decided to use a temp table to show the concept. As you will notice when reading Jeffs article the custered index, tablockx and MAXDOP hints are mandatory.
create TABLE #TestTable (Id INT, Date DATETIME, Test INT, Flag INT)
INSERT INTO #TestTable
SELECT 11,'2010-03-01',-1,NULL UNION
SELECT 12,'2010-03-02',NULL,NULL UNION
SELECT 13,'2010-03-03',0,NULL UNION
SELECT 14,'2010-03-04',1,NULL UNION
SELECT 15,'2010-03-05',NULL,NULL UNION
SELECT 16,'2010-03-06',NULL,NULL UNION
SELECT 17,'2010-03-07',1,NULL UNION
SELECT 18,'2010-03-08',NULL,NULL UNION
SELECT 19,'2010-03-09',-1,NULL UNION
SELECT 20,'2010-03-10',0,NULL UNION
SELECT 21,'2010-03-11',-1,NULL UNION
SELECT 22,'2010-03-12',1,NULL UNION
SELECT 23,'2010-03-13',NULL,NULL UNION
SELECT 24,'2010-03-14',NULL,NULL
CREATE CLUSTERED INDEX CX_#TestTable_ID ON #TestTable(ID)
DECLARE
@prev_flag INT,
@current_flag INT
SET @prev_flag = NULL
SET @current_flag = NULL
UPDATE #TestTable
SET
@prev_flag = flag = CASE WHEN @current_flag=1 OR test = 1 THEN 1 ELSE NULL END,
@current_flag =
CASE
WHEN @prev_flag = 1 AND test<> -1 THEN 1
WHEN test = -1 THEN NULL
ELSE @current_flag
END
FROM #TestTable WITH (TABLOCKX)
OPTION (MAXDOP 1)
April 17, 2010 at 3:05 pm
That is just the most awesome thing ever!!!! Works like a champ and fast as can be.
Thank you, Thank you,Thank you and Thank you!
(I will definitely read that informative posting!)
Mark!
April 17, 2010 at 3:26 pm
Ok, reading posting now... trying to figure out how to take this to my final step as still trying to understand the SQL :|.
Like to get the first 1 to stay 1 everything in between to -1 would be 0 and the last would be -1 (I was building the logic still when I first posted this)
This is what I would the range would look like.
1
0
0
0
-1
Never run across any sql like this... so still studding it.
Thanks in advance ...again :>.
Mark
April 17, 2010 at 6:38 pm
Your requirement is not clear. The way you describe it, it should already be covered with the quirky update. If not, please be a little more descriptive.
Btw: it's 2:30am over here so I'll take a nap... Maybe someone else will jump in or you'll have to wayit till tomorrow (I guess you still have some reading material... 😉 ).
April 17, 2010 at 6:42 pm
Not sure if this is the best way to do it but managed to figure it out some what :> WOHOO!
UPDATE #TestTable
SET @prev_flag = flag = CASE WHEN @current_flag IS NOT NULL OR test = 1 THEN
CASE WHEN @current_flag IS NOT NULL THEN
CASE WHEN test = -1 THEN
-1 /* ---- */
ELSE 0 /* ==== */ END
ELSE 1 /* ++++ */ END
ELSE NULL END,
@current_flag =
CASE
WHEN @prev_flag IS NOT NULL AND test<> -1 THEN 0 /* ==== */
WHEN test = -1 THEN NULL /* Now Stop It! */
ELSE @current_flag /* Carry me along */
END
FROM #TestTable WITH (TABLOCKX)
OPTION (MAXDOP 1)
Again I must say very smartly done!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply