Help with Running Totals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Running Totals

Author

Topic

berriblu
Starting Member

1 Post

Posted - 2010-10-28 : 16:29:16

Hi,

I'm completely new to SQL and I've been working on a collections report table, which consists of the Customer Code, Customer, ... 60 + days, AccBalance, % AccBalance....

I've been having trouble with two things:
The AccBalance column: finding the running total for the 60 + Days, which is sorted by the Customer's Code
The % AccBalance column: calculating the value of the AccBalance of each row over the value of the total AccBalance

I appreciate all your help!



SELECT [Customer Code], [Customer Name],
	(ISNULL([Current],0)+ISNULL([30 - 59 Days],0)+ISNULL([60 - 89 Days],0)+ ISNULL([90 - 119 Days],0)+ ISNULL([120 + Days],0)) AS [Total Balance], [Current], 
	[30 - 59 Days], [60 - 89 Days], [90 - 119 Days], [120 + Days], [60 + Days], [Acc. Balance], [6 Mon Average], [Days Delq]


FROM
(
SELECT DISTINCT NULL AS [Doc No.], NULL AS [Doc Type], T1.CARDCODE AS [Customer Code], T1.CardName AS [Customer Name], NULL AS [Due Date], 
SUM(CASE WHEN Convert(numeric, GetDate()) - convert(numeric,T0.DocDueDate)<>0 AND Convert(numeric, GetDate()) - convert(numeric,T0.DocDueDate) <=29 THEN (T0.DocTotal - T0.PaidToDate) ELSE NULL END) AS [Current], 
SUM(CASE WHEN Convert(numeric, GetDate()) - convert(numeric,T0.DocDueDate) >=30 AND Convert(numeric, GetDate()) - convert(numeric,T0.DocDueDate) <=59 THEN (T0.DocTotal - T0.PaidToDate) ELSE NULL END) AS [30 - 59 Days], 
SUM(CASE WHEN Convert(numeric, GetDate()) - convert(numeric,T0.DocDueDate) >=60 AND Convert(numeric, GetDate()) - convert(numeric,T0.DocDueDate) <=89 THEN (T0.DocTotal - T0.PaidToDate) ELSE NULL END) AS [60 - 89 Days], 
SUM(CASE WHEN Convert(numeric, GetDate()) - convert(numeric,T0.DocDueDate) >=90 AND Convert(numeric, GetDate()) - convert(numeric,T0.DocDueDate) <=119 THEN (T0.DocTotal - T0.PaidToDate) ELSE NULL END) AS [90 - 119 Days], 
SUM(CASE WHEN Convert(numeric, GetDate()) - convert(numeric,T0.DocDueDate) >=120 THEN (T0.DocTotal - T0.PaidToDate) ELSE NULL END) AS [120 + Days],
SUM(CASE WHEN Convert(numeric, GetDate()) - convert(numeric,T0.DocDueDate) >=60 THEN (T0.DocTotal - T0.PaidToDate) ELSE NULL END) AS [60 + Days],
AVG(CASE WHEN Convert(numeric, GetDate()) - convert(numeric,T0.DocDueDate) <=180 THEN (T0.DocTotal - T0.PaidToDate) ELSE NULL END) AS [6 Mon Average],
AVG(CONVERT(numeric, GetDate()) - convert(numeric,T0.DocDueDate)) AS [Days Delq]
FROM OINV T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode 
WHERE T0.DocTotal - T0.PaidToDate <> 0 and T0.[GroupNum] <> 14
GROUP BY T1.CardCode, T1.CardName
)

AS OVERALL_TABLE
ORDER BY [Customer Code], [Customer Name] DESC

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-28 : 17:07:01

You might want to look at this article:
Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)
http://www.sqlservercentral.com/articles/T-SQL/68467/

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources