# Thread: Help with Running Total

1. Registered User
Join Date
Oct 2010
Posts
1

## Unanswered: Help with Running Total

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

Code:

Code:
```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], [AccBalance], [% AccBalance], [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```

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Hi

If you are writing a report, running totals are more easily handled by the UI (Access, Excel, Reporting Services, Crystal etc.).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•