May 02, 2018 at 10:51 AM
Customer Aging Query not Given Correct Figure as per Standard Report
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]