help with running total script

Question

help with running total script

osupratt 68828

Old Hand

Points: 339
More actions
October 23, 2015 at 10:58 am

#399222

i have the following script which takes a very long time to return any records. would anyone know of an easier way to do this. i have tried sub query but can't get the correct data to return. thanks for any help you can provide.
SELECT [Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
MAX(RunningTotal)HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT DISTINCT a.[customer no_], a.[Posting Date], a.[Entry No_], a.Amount, SUM(b.Amount) AS RunningTotal
FROM [nashoudb2].[Nav_IPS_Prod].dbo.[IPS Ops$Detailed Cust_ Ledg_ Entry] AS a INNER JOIN
[nashoudb2].[Nav_IPS_Prod].dbo.[IPS Ops$Detailed Cust_ Ledg_ Entry] AS b ON a.[Entry No_] >= b.[Entry No_]
and a.[customer no_]=b.[customer no_]
WHERE YEAR(a.[posting date])=YEAR(GETDATE())
GROUP BY a.[customer no_], a.[Posting Date], a.[Entry No_], a.Amount
) AS X
GROUP BY [Customer No_]

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Read this article: http://www.sqlservercentral.com/articles/T-SQL/68467/

It explains the problem that you're facing and gives different alternatives. It's missing the 2012+ solutions, but based on the forum that you posted, you shouldn't care about them for now.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 76985 More actions · Answer 2

In SQL 2008, your best bet is to use the Quirky Update Method[/url]. In SQL 2012 and above, you can use SUM with windowing functions.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

The Quirky Update method (that Luis and Drew poster links for) is a good way to go.

It's also worth looking at Hidden RBAR: Triangular Joins[/url]. This article explains why the way you are doing your running total is so slow.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

osupratt 68828 Old Hand Points: 339 More actions · Answer 4

yikes. this looks a little much for what I am trying to do. when I have a day I can devote to this then I will check it out. thanks to all for your help.