September 11, 2009 at 8:12 am
hi there,
I'm trying to do a running total AND update a column at the same time in an update statement
UPDATE @tblComp
SET Explanation = CASE
WHEN ID IN (1,2,5,6,7,8,18,20,22,25,26) THEN ''
WHEN CP<=Guide AND ID=3 THEN ''
WHEN CP<=Guide AND ID=4 THEN ''
WHEN CP>=Guide AND ID=9 THEN ''
WHEN CP<=Guide AND ID=10 THEN ''
WHEN CP>=Guide AND ID=11 THEN ''
WHEN CP<=Guide AND ID=12 THEN ''
WHEN CP<=Guide AND ID=13 THEN ''
WHEN CP<=Guide AND ID=14 THEN ''
WHEN CP>=Guide AND ID=15 THEN ''
WHEN CP<=Guide AND ID=16 THEN ''
WHEN CP<=Guide AND ID=17 THEN ''
WHEN CP<=Guide AND ID=19 THEN ''
WHEN CP<=Guide AND ID=21 THEN ''
WHEN CP<=Guide AND ID=23 THEN ''
ELSE '*See note ' + CAST(ID AS VARCHAR(25))
END
FROM @tblComp
[/code]
Ok, so the Explanation column is either blank or it has *See note 'x' in it. The problem is that the ID will be whatever the ID is and I want it to start at 1. So instead of being *See note 14, *See note 23 I want 1 and 2
I looked over Jeff's article on running totals and rank but I think it boils down to not being able to do more than one thing in the 'ELSE' part of the statement.
thoughts, comments, suggestions?
thanks!
September 11, 2009 at 8:26 am
thoughts, comments, suggestions?
1. You appear to be using a table variable to do your running totals. You don't want to do this, because you can't establish a clustered index on a table variable. Without this index, this entire method is likely to fail.
2. You're missing some code, as I don't see the actual running total anywhere. If either CP or Guide is your running total field, you're going to need to evaluate the variable, not the field. The updates aren't written to the table row by row to be evaluated by the next row, so you can't test them on each new row. That's the whole reason you have to use a variable in the first place.
3. You can always put another case in your ELSE statement. ELSE CASE WHEN someothercriteria = something then blah END END
I'd suggest:
Rewriting the code to use a temp table and not a table variable.
Including the actual running total code in your example.
Including the WITH (INDEX(0)) hint
After that is done, and possibly after you try to add another case statement to your else (or explain why that wouldn't work, it may not meet your needs), post back and let us know what else you need help with.
September 11, 2009 at 8:42 am
I have to agree with Seth. there really isn't enough information in your post to really help you. If you could provide your code, DDL for the tables, sample data, expected results based on the sample data it would really help us help you.
For help with this, please read the first article in my signature block (it also was written by Jeff).
September 11, 2009 at 9:04 am
Lynn Pettis (9/11/2009)
I have to agree with Seth. there really isn't enough information in your post to really help you. If you could provide your code, DDL for the tables, sample data, expected results based on the sample data it would really help us help you.For help with this, please read the first article in my signature block (it also was written by Jeff).
There you have it. I might only be an Eights, but Lynn is CRAZY.
September 11, 2009 at 9:43 am
Ok, so here I was thinking I was giving more information than I needed...
CREATE TABLE #tblCompl
(
IDINTIDENTITY(1,1)
,InvestVARCHAR(75)
,GuideFLOAT
,CPFLOAT
,ExplanationVARCHAR(20)
)
data:
insert #tblCompl
SELECT 'test-a',10,10,'' union
SELECT 'test-b',10,10,'' union
SELECT 'test-c',20,30,'' union
SELECT 'test-d',10,10,'' union
SELECT 'test-e',10,10,'' union
SELECT 'test-f',30,30,'' union
SELECT 'test-g',20,20,'' union
SELECT 'test-h',10,10,'' union
SELECT 'test-i',20,30,'' union
SELECT 'test-j',30,30,''
Ok, so what I have right now, without any running total code is this for the update statement.
Update #tblComp
SET Explanation = CASE
WHEN CP<=Guide AND ID=1 THEN ''
WHEN CP<=Guide AND ID=2 THEN ''
WHEN CP=Guide AND ID=4 THEN ''
WHEN CP<=Guide AND ID=5 THEN ''
WHEN CP=Guide AND ID=7 THEN ''
WHEN CP<=Guide AND ID=8 THEN ''
WHEN CP=Guide AND ID=10 THEN ''
ELSE '*See note ' + CAST(ID AS VARCHAR(25))
END
FROM #tblComp
So it gives me
IDInvestGuideCPExplanation
1test-a1010
2test-b1010
3test-c2030*See note 3
4test-d1010
5test-e1010
6test-f3030
7test-g2020
8test-h1010
9test-i2030*See note 9
10test-j3030
when I really want is
IDInvestGuideCPExplanation
1test-a1010
2test-b1010
3test-c2030*See note 1
4test-d1010
5test-e1010
6test-f3030
7test-g2020
8test-h1010
9test-i2030*See note 2
10test-j3030
what can I do with the update statement to get it what I'm looking for. I basically only want to increment the counter in the ELSE
September 11, 2009 at 9:52 am
Garadin (9/11/2009)
You appear to be using a table variable to do your running totals. You don't want to do this, because you can't establish a clustered index on a table variable. Without this index, this entire method is likely to fail.
:blink:
DECLARE @Clustered1 TABLE (A INTEGER IDENTITY PRIMARY KEY CLUSTERED);
DECLARE @Clustered2 TABLE (A INTEGER IDENTITY UNIQUE CLUSTERED);
🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 11, 2009 at 10:13 am
Paul White (9/11/2009)
Garadin (9/11/2009)
You appear to be using a table variable to do your running totals. You don't want to do this, because you can't establish a clustered index on a table variable. Without this index, this entire method is likely to fail.:blink:
DECLARE @Clustered1 TABLE (A INTEGER IDENTITY PRIMARY KEY CLUSTERED);
DECLARE @Clustered2 TABLE (A INTEGER IDENTITY UNIQUE CLUSTERED);
🙂
...
Man, I swear there was a reason you couldn't use table variables for these things and I thought that was it.
Ack, no, it was table hints. You can't specify the index to use on a table variable. So it can be created, it just can't be explicitly specified.
Thanks for the correction Paul 🙂
September 11, 2009 at 10:22 am
You can use the same evaluation method to decide whether or not to increment the variable for your MessageID.
DECLARE @ID INT
SET @ID = 0
Update #tblCompl
SET Explanation = CASE
WHEN CP<=Guide AND ID=1 THEN ''
WHEN CP<=Guide AND ID=2 THEN ''
WHEN CP=Guide AND ID=4 THEN ''
WHEN CP<=Guide AND ID=5 THEN ''
WHEN CP=Guide AND ID=7 THEN ''
WHEN CP<=Guide AND ID=8 THEN ''
WHEN CP=Guide AND ID=10 THEN ''
ELSE '*See note ' + CAST(@ID AS VARCHAR(25))
END,
@ID = CASE WHEN
( CP<=Guide AND ID=1 )
OR ( CP<=Guide AND ID=2 )
OR ( CP=Guide AND ID=4 )
OR ( CP<=Guide AND ID=5 )
OR ( CP=Guide AND ID=7 )
OR ( CP<=Guide AND ID=8 )
OR ( CP=Guide AND ID=10)
THEN @ID ELSE @ID + 1 END
FROM #tblCompl WITH (INDEX(0))
[Edit] Minor Corrections
September 11, 2009 at 10:43 am
thanks all
September 11, 2009 at 9:36 pm
Garadin (9/11/2009)
1. You appear to be using a table variable to do your running totals. You don't want to do this, because you can't establish a clustered index on a table variable. Without this index, this entire method is likely to fail.
Actually, you can. PK will be clustered.
2. You're missing some code, as I don't see the actual running total anywhere.[/quote]
Whew! Thought something really bad happened to the machine I'm using this week.
--Jeff Moden
September 11, 2009 at 9:40 pm
Agh... sorry... Paul beat me to it. I didn't read the whole thread before I posted.
--Jeff Moden
September 11, 2009 at 9:44 pm
Jeff Moden (9/11/2009)
Agh... sorry... Paul beat me to it. I didn't read the whole thread before I posted.
Good to know it happens to you too :laugh:
I just posted something on one of today's featured articles. Thought I had read the whole thread, turned out there were other pages...sigh.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply