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 ««123»»

Running Total Expand / Collapse
Author
Message
Posted Friday, January 17, 2014 10:04 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316
refer to this post...

http://sqlsaga.com/sql-server/how-to-calculate-running-totals-in-sql-server/

step by step explained...


Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1532349
Posted Saturday, January 18, 2014 9:24 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
Ford Fairlane (1/15/2014)
Refined and resolved !!!

Thanks for all your input.

This is the working query.

SELECT
YEAR(CAST (a.ENDDATE AS DATETIME)),
MONTH(CAST (a.ENDDATE AS DATETIME)),
(

SELECT
SUM(b.DEPTH)

FROM
HOLELOCATION b

WHERE
b.HOLEID LIKE 'RHRC%'
AND ( CAST(YEAR(CAST(b.ENDDATE AS DATETIME)) AS VARCHAR) + RIGHT('00'+ CAST( MONTH(CAST (b.ENDDATE AS DATETIME)) AS VARCHAR(2)),2)) <= ( CAST(YEAR(CAST(a.ENDDATE AS DATETIME)) AS VARCHAR) + RIGHT('00'+ CAST( MONTH(CAST (a.ENDDATE AS DATETIME)) AS VARCHAR(2)),2))
AND YEAR(CAST (b.ENDDATE AS DATETIME)) IS NOT NULL AND MONTH(CAST (b.ENDDATE AS DATETIME)) IS NOT NULL

) AS DEPTH

FROM
HOLELOCATION a

WHERE a.HOLEID LIKE 'RHRC%' AND a.ENDDATE IS NOT NULL

GROUP BY
YEAR(CAST (a.ENDDATE AS DATETIME)),
MONTH(CAST (a.ENDDATE AS DATETIME))

ORDER BY
YEAR(CAST (a.ENDDATE AS DATETIME)) ASC,
MONTH(CAST (a.ENDDATE AS DATETIME)) ASC


That will certainly work but be advised that it's not scalable. If you end up with a not so large number of rows, it will eat the face off your server because it uses a thing known as a "Triangular Join", which is about half as bad as a full Cartesian Join (also known as a "Square Join").

Please see the following article on Triangular Joins.
http://www.sqlservercentral.com/articles/T-SQL/61539/


--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."

(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 #1532383
Posted Saturday, January 18, 2014 9:28 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
a4apple (1/17/2014)
refer to this post...

http://sqlsaga.com/sql-server/how-to-calculate-running-totals-in-sql-server/

step by step explained...


Same thing there. It's a Triangular Join. No matter what, it will always use more resources than you could possibly imagine. In pre-2012 SQL Server, it's far better to learn how to use a "Quirky Update" correctly. If you take exception to that unsupported method, then write a Cursor or While loop to do the running total. See the following URL for more on the problems with Triangular Joins.
http://www.sqlservercentral.com/articles/T-SQL/61539/


--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."

(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 #1532384
Posted Saturday, January 18, 2014 5:23 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 927, Visits: 5,897
Is the same true if you use a common table expression to calculate the running total?
Post #1532416
Posted Saturday, January 18, 2014 9:45 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
pietlinden (1/18/2014)
Is the same true if you use a common table expression to calculate the running total?


Yes except that it's likely that the CTE will use more reads than a Cursor or While Loop.


--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."

(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 #1532422
Posted Sunday, January 19, 2014 5:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:30 AM
Points: 1,804, Visits: 783
Thanks for the heads up Jeff, how would you tackle it then ?

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Post #1532481
Posted Tuesday, January 21, 2014 6:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 391, Visits: 2,679
Awesome link here discussing various methods:

http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance
Post #1533031
Posted Tuesday, January 21, 2014 7:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
Ford Fairlane (1/19/2014)
Thanks for the heads up Jeff, how would you tackle it then ?


Start by reducing the noise to see what's going on:
SELECT
x.YearMonth,
[DEPTH] = (
SELECT
SUM(b.DEPTH)
FROM HOLELOCATION b
CROSS APPLY (SELECT [YearMonth] = CONVERT(CHAR(6),CAST(b.ENDDATE AS DATETIME), 112)) x2
WHERE b.HOLEID LIKE 'RHRC%'
AND x2.YearMonth <= x.YearMonth
)
FROM HOLELOCATION a
CROSS APPLY (SELECT [YearMonth] = CONVERT(CHAR(6),CAST(a.ENDDATE AS DATETIME), 112)) x
WHERE a.HOLEID LIKE 'RHRC%'
AND x.YearMonth IS NOT NULL
GROUP BY x.YearMonth
ORDER BY x.YearMonth



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1533051
Posted Thursday, January 23, 2014 10:16 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
Ford Fairlane (1/19/2014)
Thanks for the heads up Jeff, how would you tackle it then ?


I use the Quirky Update method and will probably continue to do so even after my company upgrades to 2012 and then to 2014. It's not for the faint of heart and there are a shedload of rules to follow but, for the size of the tables I used it for, it was worth it. It'll do a running total on a million row table (comparatively small nowadays) in just a couple of seconds even if you use the more complicated version with built in "sort order verification".

As an alternative, if the table that you need running totals for is a WORM table (write once, read many... like a check book where corrections are added in as additional transactions) AND you don't mind storing the running total, it might be better to use a trigger or some other code to calculate the running totals just for the new rows based on the previous maximum record.


--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."

(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 #1534338
Posted Thursday, January 23, 2014 11:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:30 AM
Points: 1,804, Visits: 783
Thanks Jeff, appreciate the advice.

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Post #1534351
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse