Totals to date

Question

Totals to date

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

It does - and so your error would not seem to make sense. How are you running this?

I sometimes just highlight what I want to try, and hit F5: note that if I did that, and didn't highlight the DECLARE parts as WELL as the update part - the Error you mentioned would happen.

Also - if you put GO anywhere between the DECLARE and the use of a variable - that eror will happen to. GO is a "batch terminator" which also signifies that it's the end of the scope of any locally declared variables.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

myschif Say Hey Kid Points: 695 More actions · Answer 2

If I run the whole script it works fine; I was trying to step through it and Matt's assumption was correct. I did not highlight the update section with the declare section. Thank you Adam and Matt.

May I ask how to remove the "reset" functionality:

--===== Changed the formula to "reset" when the account changes

UPDATE #T

SET @PrevGrpBal = AMOUNT = CASE

WHEN ID = @PrevAcctID

THEN @PrevGrpBal + Amount

ELSE Amount -- Restarts total at "0 + current amount"

END,

@PrevAcctID = ID

FROM #T WITH (INDEX(IX_T_ID_Month_Year),TABLOCKX)

I need to keep a running "grand total"

Thank you for your assistance.

"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

If you do not need the totals grouped, you dont need the case or the id column.

your update should look like this

UPDATE #T

SET @PrevGrpBal = AMOUNT = @PrevGrpBal + Amount

My blog: http://jahaines.blogspot.com

myschif Say Hey Kid Points: 695 More actions · Answer 4

I have it working in my stored proc. Your knowledge and this forum give me hope. Thank you all for your assistance. 😀

"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Ummm... looking back at some of the code in this thread...

Make sure that the INDEX you're using in the hint is a clustered index... there's the possibility that a non-clustered index could go into a "Merry-go-round" mode that could actually give you an out of sequence running total (nice way of saying it might give you the wrong answer).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

Thanks for pointing that out. It makes sense as the clustered index actually affects the sorting of the data.

In this case, the sorting of the data is irrelevant as the update goes through all temp table rows and does not group. I will definitely note that the index should be clustered for future running totals.

Thanks.

My blog: http://jahaines.blogspot.com

Adam Haines SSC-Insane Points: 23217 More actions · Answer 7

mischif,

You should change the nonclustered index to clustered, per Jeff's recommendation. This way you ensure that everything is sorted correctly.

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

Adam Haines (2/26/2008)
Thanks for pointing that out. It makes sense as the clustered index actually affects the sorting of the data.
In this case, the sorting of the data is irrelevant as the update goes through all temp table rows and does not group. I will definitely note that the index should be clustered for future running totals.
Thanks.

Understood... but what order do you want to reflect in the running total?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 9

Adam Haines (2/26/2008)
--------------------------------------------------------------------------------
Thanks for pointing that out. It makes sense as the clustered index actually affects the sorting of the data.
In this case, the sorting of the data is irrelevant as the update goes through all temp table rows and does not group. I will definitely note that the index should be clustered for future running totals.
Thanks.
Understood... but what order do you want to reflect in the running total?
--Jeff Moden

To be honest, I posted this statement last night without a lot of real thought. It was late and rushed to conclusion without really thinking about what I was saying. While using rational thought this AM 🙂 I will answer you, "I would assume that the OP wants to return the data in a date / period order, which would require an appropriate clustered index."

I would also go on to say, the only way order is truly irrelevant is if you only want the total.

Thanks again for the clarfication.

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

Heh,... yeah, coffee helps me too 😀 Thanks for the feedback, Adam...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)