Skip to Content
0

Customer Aging Query not Given Correct Figure as per Standard Report

May 02 at 10:51 AM

58

avatar image

Hi experts

Am having a sql query for Customized report with Customer Aging

it is not returning me the correct value as per the Standard Aging Report

I use Multi Currency for my Customer, but need Aging in local Currency

i had some fields with it

also i need to filter the query with GlLicnumber (in which i had given the collection resp person name)

Can any one help me to edit this query and help me to sort it out

Regards


SELECT tt.[Cust Num],
       tt.[Cust Name],
	   tt.[Responsible for Collection],
	   tt.[Balance],
	   tt.[GroupCode],
	   --tt.[Last Payment Received],
	  -- tt.[Last Payment Received Date],
	   tt.[PymntGroup],
	   --tt.[AliasName],
	   tt.[city],
	   


       --SUM(tt.[Credit Amt]) AS [Credit Amt],
       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.[121 Plus Days]) AS [121 Plus Days]
FROM   (
           SELECT T1.CardCode AS 'Cust Num',
                  T1.CardName AS 'Cust Name',
				  T1.GlblLocNum AS 'Responsible for Collection',
                  T0.BalDueDeb AS 'Debit Amt',
                  T0.BalDueCred * -1 AS 'Credit Amt',
				  T1.Balance AS 'Balance',
				  T1.[GroupCode],
				  T2.[PymntGroup],
				  --T1.[AliasName],
				  T3.[City],
				  


				  IsNull((Select Top 1 Sum(DocTotal)  From ORCT Where ORCT.CardCode=T1.CardCode and ORCT.Status <> 'C' and (ORCT.DocDate>= ORCT.DocDate and ORCT.DocDate<= ORCT.DocDate) Group By ORCT.DocDate Order By ORCT.DocDate DESC),0) 'Last Payment Received',
                  IsNull((Select Top 1 Max(DocDate)  From ORCT Where ORCT.CardCode=T1.CardCode and ORCT.Status <> 'C' and (ORCT.DocDate>= ORCT.DocDate and ORCT.DocDate<= ORCT.DocDate ) Group By ORCT.DocDate Order By ORCT.DocDate DESC),0) 'Last Payment Received Date',
                  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 < 30 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 > 121 THEN CASE
                                                                                    WHEN
                                                                                         balduecred
                                                                                         <
                                                                                         >
                                                                                         0 THEN
                                                                                         balduecred
                                                                                         *
                                                                                         -
                                                                                         1
                                                                                    ELSE
                                                                                         balduedeb
                                                                               END
           END AS '121 Plus Days'
          
           FROM JDT1 T0
          
           INNER JOIN OCRD T1
           ON T0.ShortName = T1.CardCode
      AND T1.CardType = 'C'/* AND T1.QryGroup1 = 'Y'*/
	  inner join OCTG T2 ON T1.GroupNum = T2.GroupNum
	  LEFT OUTER JOIN CRD1 T3 ON T1.City = T3.City
          
           WHERE
                                 
           T1.CardType ='C' /*AND T1.CARDCODE NOT IN  ('DB00000063','JB00000145','RB00000465')  */AND
          
           T0.IntrnMatch = '0'
           AND T0.BalDueDeb != T0.BalDueCred AND T1.Balance <> '0'  /*and t1.CardCode = 'RB0000117' */ and T1.GlblLocNum = 'SAFDAR'
       ) AS tt 
        
GROUP BY
       tt.[Cust Num],
       tt.[Cust Name],
	   tt.[Balance],
	   tt.[GroupCode],
	   tt.[Last Payment Received],
	   tt.[Last Payment Received Date],
	   tt.[Responsible for Collection],
	   tt.[PymntGroup],
	   --tt.[AliasName]
	   tt.[CITY]
       
Order By tt.[Cust Name]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Nagarajan K May 02 at 02:27 PM
0

Hi,

Try this query. The problem in your query link between OCRD and CRD1 tables

SELECT tt.[Cust Num], tt.[Cust Name], tt.[Responsible for Collection], tt.[Balance], tt.[GroupCode], --tt.[Last Payment Received], -- tt.[Last Payment Received Date], tt.[PymntGroup], --tt.[AliasName], tt.[city], --SUM(tt.[Credit Amt]) AS [Credit Amt], 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.[121 Plus Days]) AS [121 Plus Days]

FROM ( SELECT T1.CardCode AS 'Cust Num', T1.CardName AS 'Cust Name', T1.GlblLocNum AS 'Responsible for Collection', T0.BalDueDeb AS 'Debit Amt', T0.BalDueCred * -1 AS 'Credit Amt', T1.Balance AS 'Balance', T1.[GroupCode], T2.[PymntGroup], --T1.[AliasName], T3.[City], IsNull((Select Top 1 Sum(DocTotal) From ORCT Where ORCT.CardCode=T1.CardCode and ORCT.Status <> 'C' and (ORCT.DocDate>= ORCT.DocDate and ORCT.DocDate<= ORCT.DocDate) Group By ORCT.DocDate Order By ORCT.DocDate DESC),0) 'Last Payment Received', IsNull((Select Top 1 Max(DocDate) From ORCT Where ORCT.CardCode=T1.CardCode and ORCT.Status <> 'C' and (ORCT.DocDate>= ORCT.DocDate and ORCT.DocDate<= ORCT.DocDate ) Group By ORCT.DocDate Order By ORCT.DocDate DESC),0) 'Last Payment Received Date', 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 < 30 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 > 121 THEN CASE WHEN balduecred < > 0 THEN balduecred * - 1 ELSE balduedeb END END AS '121 Plus Days'

FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode AND T1.CardType = 'C' /* AND T1.QryGroup1 = 'Y'*/ inner join OCTG T2 ON T1.GroupNum = T2.GroupNum INNER JOIN CRD1 T3 ON T1.cardcode = T3.cardcode and T1.[BillToDef] = T3.[Address] WHERE T1.CardType ='C' /*AND T1.CARDCODE NOT IN ('DB00000063','JB00000145','RB00000465') */AND T0.IntrnMatch = '0' AND T0.BalDueDeb != T0.BalDueCred AND T1.Balance <> '0' --/*and t1.CardCode = 'RB0000117' */ and T1.GlblLocNum = 'SAFDAR' ) AS tt

GROUP BY tt.[Cust Num], tt.[Cust Name], tt.[Balance], tt.[GroupCode], tt.[Last Payment Received], tt.[Last Payment Received Date], tt.[Responsible for Collection], tt.[PymntGroup], --tt.[AliasName] tt.[CITY] Order By tt.[Cust Name]

Regards,

Nagarajan

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Sir

am getting error when i run this query

Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'FROM'. Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'FROM'.

0

sir

i try to fix the error,,,still the same issue and same error

regards

0
Nagarajan K May 02 at 02:28 PM
0

Hi,

Try this query. The problem in your query link between OCRD and CRD1 tables

SELECT tt.[Cust Num], tt.[Cust Name], tt.[Responsible for Collection], tt.[Balance], tt.[GroupCode], --tt.[Last Payment Received], -- tt.[Last Payment Received Date], tt.[PymntGroup], --tt.[AliasName], tt.[city], --SUM(tt.[Credit Amt]) AS [Credit Amt], 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.[121 Plus Days]) AS [121 Plus Days]

FROM ( SELECT T1.CardCode AS 'Cust Num', T1.CardName AS 'Cust Name', T1.GlblLocNum AS 'Responsible for Collection', T0.BalDueDeb AS 'Debit Amt', T0.BalDueCred * -1 AS 'Credit Amt', T1.Balance AS 'Balance', T1.[GroupCode], T2.[PymntGroup], --T1.[AliasName], T3.[City], IsNull((Select Top 1 Sum(DocTotal) From ORCT Where ORCT.CardCode=T1.CardCode and ORCT.Status <> 'C' and (ORCT.DocDate>= ORCT.DocDate and ORCT.DocDate<= ORCT.DocDate) Group By ORCT.DocDate Order By ORCT.DocDate DESC),0) 'Last Payment Received', IsNull((Select Top 1 Max(DocDate) From ORCT Where ORCT.CardCode=T1.CardCode and ORCT.Status <> 'C' and (ORCT.DocDate>= ORCT.DocDate and ORCT.DocDate<= ORCT.DocDate ) Group By ORCT.DocDate Order By ORCT.DocDate DESC),0) 'Last Payment Received Date', 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 < 30 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 > 121 THEN CASE WHEN balduecred < > 0 THEN balduecred * - 1 ELSE balduedeb END END AS '121 Plus Days'

FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode AND T1.CardType = 'C' /* AND T1.QryGroup1 = 'Y'*/ inner join OCTG T2 ON T1.GroupNum = T2.GroupNum INNER JOIN CRD1 T3 ON T1.cardcode = T3.cardcode and T1.[BillToDef] = T3.[Address] WHERE T1.CardType ='C' /*AND T1.CARDCODE NOT IN ('DB00000063','JB00000145','RB00000465') */AND T0.IntrnMatch = '0' AND T0.BalDueDeb != T0.BalDueCred AND T1.Balance <> '0' --/*and t1.CardCode = 'RB0000117' */ and T1.GlblLocNum = 'SAFDAR' ) AS tt

GROUP BY tt.[Cust Num], tt.[Cust Name], tt.[Balance], tt.[GroupCode], tt.[Last Payment Received], tt.[Last Payment Received Date], tt.[Responsible for Collection], tt.[PymntGroup], --tt.[AliasName] tt.[CITY] Order By tt.[Cust Name]

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K May 05 at 06:44 AM
0

Hi,

The problem is when i copy above query here the format is not correct and causing the error.

Try with attached query,

query.txt

Regards,

Nagarajan


query.txt (3.3 kB)
Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thanks rajan

This works fine

0

Close this thread if you got answer.

0
Nagarajan K May 04 at 01:42 PM
0

Hi,

Try this,

SELECT tt.[Cust Num], tt.[Cust Name], tt.[Responsible for Collection], tt.[Balance], tt.[GroupCode], --tt.[Last Payment Received], -- tt.[Last Payment Received Date], tt.[PymntGroup], --tt.[AliasName], tt.[city], SUM(tt.[Credit Amt]) AS [Credit Amt], 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.[121 Plus Days]) AS [121 Plus Days] FROM ( SELECT t1.cardcode AS 'Cust Num', t1.cardname AS 'Cust Name', t1.glbllocnum AS 'Responsible for Collection', t0.balduedeb AS 'Debit Amt', t0.balduecred * -1 AS 'Credit Amt', t1.balance AS 'Balance', t1.[GroupCode], t2.[PymntGroup], --T1.[AliasName], T3.[City], IsNull((Select Top 1 Sum(DocTotal) From ORCT Where ORCT.CardCode=T1.CardCode and ORCT.Status <> 'C' and (ORCT.DocDate>= ORCT.DocDate and ORCT.DocDate<= ORCT.DocDate) Group By ORCT.DocDate Order By ORCT.DocDate DESC),0) 'Last Payment Received', IsNull((Select Top 1 Max(DocDate) From ORCT Where ORCT.CardCode=T1.CardCode and ORCT.Status <> 'C' and (ORCT.DocDate>= ORCT.DocDate and ORCT.DocDate<= ORCT.DocDate ) Group By ORCT.DocDate Order By ORCT.DocDate DESC),0) 'Last Payment Received Date', 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 < 30 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 > 121 THEN CASE WHEN balduecred < > 0 THEN balduecred * - 1 ELSE balduedeb END END AS '121 Plus Days' FROM jdt1 T0 INNER JOIN ocrd T1 ON t0.shortname = t1.cardcode AND t1.cardtype = 'C' /* AND T1.QryGroup1 = 'Y'*/ INNER JOIN octg T2 ON t1.groupnum = t2.groupnum INNER JOIN crd1 T3 ON t1.cardcode = t3.cardcode AND t1.[BillToDef] = t3.[Address] WHERE t1.cardtype ='C' /*AND T1.CARDCODE NOT IN ('DB00000063','JB00000145','RB00000465') */ AND t0.intrnmatch = '0' AND t0.balduedeb != t0.balduecred AND t1.balance <> '0' ) AS tt GROUP BY tt.[Cust Num], tt.[Cust Name], tt.[Balance], tt.[GroupCode], tt.[Last Payment Received], tt.[Last Payment Received Date], tt.[Responsible for Collection], tt.[PymntGroup], --tt.[AliasName] tt.[CITY] Order By tt.[Cust Name]

Regards,

Nagarajan

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Sir

still error pop up, i try to fix the Error which is mention, but not able to understand the issue

Regards

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '>'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'Order'.

0