Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««2526272829

Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten) Expand / Collapse
Author
Message
Posted Monday, February 13, 2012 11:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:56 AM
Points: 1,639, Visits: 16,545
gcresse (2/13/2012)
I just read this article about SQL Server 2012's new features (http://blog.tallan.com/2011/12/08/sql-server-2012-windowing-functions-part-1-of-2-running-and-sliding-aggregates/) and I'm thinking it *might* solve my problem, as long as I can join to other tables. I had to give up on the Quirky Update because I had to try to jam every column I needed into the same row and in some cases the row was too wide for SQL Server to handle.


whilst you wait for 2012.....could you consider creating a temp table from all of your sub queries / joins etc....and then running QU on the temp table?


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1251362
Posted Monday, February 13, 2012 12:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:56 PM
Points: 77, Visits: 392
The temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.
Post #1251457
Posted Monday, February 13, 2012 12:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:56 AM
Points: 1,639, Visits: 16,545
gcresse (2/13/2012)
The temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.


ok.. I cant see what you can see......but in your situation then I would be asking myself "why have I got too many columns...should I be thinking about rows instead?"...I assume this is what you mean by "not be wide enough"

anyway good luck.

kind regards


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1251466
Posted Monday, February 13, 2012 5:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
J Livingston SQL (2/13/2012)
gcresse (2/13/2012)
The temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.


ok.. I cant see what you can see......but in your situation then I would be asking myself "why have I got too many columns...should I be thinking about rows instead?"...I assume this is what you mean by "not be wide enough"

anyway good luck.

kind regards


I agree... think rows instead of columns here even if you don't use the QU.

As a sidebar, any process that has thousands of steps should probably be reevaluated a bit.

As another sidebar, can you move the discussion about such a wide table to a new thread, please? Thanks.


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1251610
Posted Friday, March 14, 2014 5:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 6:51 AM
Points: 250, Visits: 531
Jeff - on behalf of my client

see http://www.sqlservercentral.com/Forums/Topic1550386-392-1.aspx

I took a stored procedure, modified (but not enough) which had used cursors in Oracle that ran for 88 minutes 2 nights ago
last night it ran in 4 minutes
you are so right RBAR is truly evil
Post #1551094
« Prev Topic | Next Topic »

Add to briefcase «««2526272829

Permissions Expand / Collapse