Results 1 to 2 of 2
  1. #1
    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


    I appreciate all your help!

    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. #2
    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.).
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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