Customer aging report

[Former Member]

Hi All,

I need to get a customer aging report through a query. showing open invoices and credits.report should contain

customercode, customername, type, doc no, posting date, due date, bp ref no, balance due.  Can someone show me the query how to get this information please.  In they query i should be able to pass the customer code.

[Former Member · Accepted solution]

Hi,

Try this

SELECT tt.[Cust Num],

       tt.[Cust Name],

       tt.[Cust Group],

       tt.[Posting Date],

    tt.[Due Date],

       SUM(tt.[0-30 Days]) AS [0-30 Days],

       SUM(tt.[31 to 60 Days]) AS [31 to 60 Days],

       SUM(tt.[61 to 90 days]) AS [61 to 90 Days],

       SUM(tt.[90 to 120 Days]) AS [91 to 120 Days],

       SUM(tt.[120 Plus Days]) AS [120 Plus Days]

FROM   (

           SELECT T1.CardCode AS 'Cust Num',

                  T1.CardName AS 'Cust Name',

                  T2.GroupName AS 'Cust Group',

                  T0.BalDueDeb AS 'Debit Amt',

                  T0.BalDueCred * -1 AS 'Credit Amt',

                  CASE

                       WHEN T0.TransType = 13 THEN 'AR Invoice'

                       WHEN T0.TransType = 14 THEN 'AR Cred Memo'

                       WHEN T0.TransType = 24 THEN 'Payment'

                       ELSE 'Other'

                  END AS 'Trans Type',

                  T0.Ref1 AS 'Reference',

                  FcCurrency AS 'Currency',

                  CONVERT(VARCHAR(10), RefDate, 103) 'Posting Date',

                  CONVERT(VARCHAR(10), DueDate, 103) 'Due Date',

                  CONVERT(VARCHAR(10), TaxDate, 103) 'Doc Date',

                  CASE

                       WHEN (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 < 31 THEN CASE

                                                                                          WHEN

                                                                                               balduecred

                                                                                               <

                                                                                               >

                                                                                               0 THEN

                                                                                               balduecred

                                                                                               *

                                                                                               -

                                                                                               1

                                                                                          ELSE

                                                                                               balduedeb

                                                                                     END

                  END AS '0-30 Days',

                  CASE

                       WHEN (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 > 30

           AND (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 < 61 THEN

               CASE

                    WHEN balduecred < > 0 THEN balduecred * -1

                    ELSE balduedeb

               END

               END AS '31 to 60 Days',

           CASE

                WHEN (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 > 60

           AND (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 < 91 THEN

               CASE

                    WHEN balduecred < > 0 THEN balduecred * -1

                    ELSE balduedeb

               END

               END AS '61 to 90 days',

           CASE

                WHEN (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 > 90

           AND (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 < 121 THEN

               CASE

                    WHEN balduecred < > 0 THEN balduecred * -1

                    ELSE balduedeb

               END

               END AS '90 to 120 Days',

           CASE

                WHEN (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 > 120 THEN CASE

                                                                                    WHEN

                                                                                         balduecred

                                                                                         <

                                                                                         >

                                                                                         0 THEN

                                                                                         balduecred

                                                                                         *

                                                                                         -

                                                                                         1

                                                                                    ELSE

                                                                                         balduedeb

                                                                               END

           END AS '120 Plus Days'

           FROM JDT1 T0

           INNER JOIN OCRD T1

           ON T0.ShortName = T1.CardCode

      AND T1.CardType = 'C'

  INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode

           WHERE

           T0.IntrnMatch = '0'

           AND T0.BalDueDeb != T0.BalDueCred

       ) AS tt

GROUP BY

       tt.[Cust Num],

       tt.[Cust Name],

       tt.[Cust Group],

       tt.[Posting Date],

    tt.[Due Date]

Regards,

SP Samy

[Former Member · Accepted solution]

Hi Samy

I am trying to achieve this.  Showing the Balance to the customer.  I am attaching an excel format.

[former_member188586 · Accepted solution]

hi try with bellow query once

SELECT T0.[CardCode], T0.[CardName],

(SELECT SUM(a.[DocTotal]-a.[PaidToDate])  FROM OINV a WHERE a.[DocDueDate]<getdate() and a.[DocDueDate]>=(select dateadd(dd,-30,getdate())) and a.[CardCode]=T0.[CardCode]  AND a.[DocStatus]<>'C') as ' 0-30 Days',

(SELECT SUM(a.[DocTotal]-a.[PaidToDate]) FROM OINV a WHERE a.[DocDueDate]<(select dateadd(dd,-30,getdate())) and a.[DocDueDate]>=(select dateadd(dd,-60,getdate()))    and a.[CardCode]=T0.[CardCode]  AND a.[DocStatus]<>'C') as ' 30-60 Days',

(SELECT SUM(a.[DocTotal]-a.[PaidToDate]) FROM OINV a WHERE a.[DocDueDate]<(select dateadd(dd,-60,getdate())) and a.[DocDueDate]>=(select dateadd(dd,-90,getdate()))    and a.[CardCode]=T0.[CardCode]  AND a.[DocStatus]<>'C') as ' 60-90 Days',

(SELECT SUM(a.[DocTotal]-a.[PaidToDate]) FROM OINV a WHERE a.[DocDueDate]<(select dateadd(dd,-90,getdate())) and a.[DocDueDate]>=(select dateadd(dd,-120,getdate()))    and a.[CardCode]=T0.[CardCode]  AND a.[DocStatus]<>'C') as ' 90-120 Days',

(SELECT SUM(a.[DocTotal]-a.[PaidToDate]) FROM OINV a WHERE a.[DocDueDate]<(select dateadd(dd,-120,getdate())) and a.[CardCode]=T0.[CardCode]  AND a.[DocStatus]<>'C') as ' above Days' FROM OCRD T0 WHERE  T0.[Balance] <>'0' and T0.[CardName] NOT LIKE 'Duplicate%'

--Ramudu

[Former Member · Accepted solution]

Hi Ram,

I am trying to achieve the following format.  Once i enter the dealer code in where clause i should be able to get the following format.  Can you help me with this please.