on 05-26-2015 3:58 PM
Can someone help me fix my query so the aging buckets are reflected correctly. Currently when there is a balance on account, the amount is not correct for the balance on account. Then the totals per bucket do not match the balance and what shows on the BP system report in SAP. Is this a simple fix? Below is what I have which is aging summary with remarks and payment terms. SELECT CardCode, CardName, PymntGroup, SUM(Balance) BALANCE, SUM(A) FUTURE, SUM(B) '0-30', SUM(C) '31-60', SUM(D) '61-90', SUM(E) '91-120', SUM(F) '121+',Rmk FROM ( SELECT T1.CardCode, T1.CardName, T2.[PymntGroup], T0.RefDate, T0.Ref1 'Document_Number', CASE WHEN T0.TransType=13 THEN 'Invoice' WHEN T0.TransType=14 THEN 'Credit Note' WHEN T0.TransType=30 THEN 'Journal' WHEN T0.TransType=24 THEN 'Receipt' END AS 'Document_Type', T0.DueDate, (T0. [BalScDeb]- T0.[BalScCred]) 'Balance' , CAST(T1. [Free_Text] as varchar(250)) as Rmk ,ISNULL((SELECT T0.[SYSDeb] - T0.[SYSCred] WHERE DateDiff(day, T0.DueDate, getdate())=0 and DateDiff(day, T0.DueDate,getdate())30 and DateDiff(day, T0.DueDate,getdate())60 and DateDiff(day, T0.DueDate,getdate())90 and DateDiff(day, T0.DueDate,getdate())=121),0) 'F' FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode Left outer join OCTG T2 On T1.GroupNum = T2.GroupNum WHERE T1.CardType = 'C' and BalSccred + balscdeb <>0 ) T100 GROUP BY CARDCODE, CARDNAME, PymntGroup, Rmk ORDER BY CARDCODE
What I notice is the aging shows reconciled amounts that net to zero I had though becasue of the logic below it would not due that. And that seems to be the issue between the aging buckets in my query and what shows in the canned report in SAP business one. Can anyone help?? SELECT CardCode, CardName, PymntGroup, SUM(Balance) BALANCE, SUM(A) FUTURE, SUM(B) '0-30', SUM(C) '31-60', SUM(D) '61-90', SUM(E) '91-120', SUM(F) '121+',Rmk, LastPmtDate, LastPmtAmt FROM ( SELECT T1.CardCode, T1.CardName, T2.[PymntGroup], T0.RefDate, T0.Ref1 'Document_Number', CASE WHEN T0.TransType=13 THEN 'Invoice' WHEN T0.TransType=14 THEN 'Credit Note' WHEN T0.TransType=30 THEN 'Journal' WHEN T0.TransType=24 THEN 'Receipt' END AS 'Document_Type', T0.DueDate, (T0. [BalScDeb]- T0.[BalScCred]) 'Balance' , CAST(T1. [Free_Text] as varchar(900)) as Rmk ,ISNULL((SELECT T0.[SYSDeb] - T0.[SYSCred] WHERE DateDiff(day, T0.DueDate, getdate())=0 and DateDiff(day, T0.DueDate,getdate())30 and DateDiff(day, T0.DueDate,getdate())60 and DateDiff(day, T0.DueDate,getdate())90 and DateDiff(day, T0.DueDate,getdate())=121),0) 'F' , t.DocDate AS LastPmtDate , t.[DocTotalSy] AS LastPmtAmt FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode Left outer join OCTG T2 On T1.GroupNum = T2.GroupNum LEFT JOIN (SELECT MAX(DocEntry) AS LastPmt, CardCode FROM ORCT GROUP BY CardCode) p ON T1.CardCode = p.CardCode LEFT JOIN ORCT t ON p.CardCode = t.CardCode AND p.LastPmt = t.DocEntry WHERE T1.CardType = 'C' and BalSccred + balscdeb <>0) T100 GROUP BY CARDCODE, CARDNAME, PymntGroup, Rmk, LastPmtDate, LastPmtAmt ORDER BY CARDCODE
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The issue is the balance seems correct but I want the aging buckets to match what is in SAP on the aging report. IT seem like noone can match the aging report that comes from the system. Let me know if that is possible. This query also has other info beside the aging but that should not be an issue. Thanks Danielle
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
SELECT CardCode, CardName, PymntGroup, SUM(Balance) BALANCE, SUM(A) FUTURE, SUM(B) '0-30', SUM(C) '31-60', SUM(D) '61-90', SUM(E) '91-120', SUM(F) '121+',Rmk, LastPmtDate, LastPmtAmt FROM ( SELECT T1.CardCode, T1.CardName, T2.[PymntGroup], T0.RefDate, T0.Ref1 'Document_Number', CASE WHEN T0.TransType=13 THEN 'Invoice' WHEN T0.TransType=14 THEN 'Credit Note' WHEN T0.TransType=30 THEN 'Journal' WHEN T0.TransType=24 THEN 'Receipt' END AS 'Document_Type', T0.DueDate, (T0. [BalScDeb]- T0.[BalScCred]) 'Balance' , CAST(T1. [Free_Text] as varchar(250)) as Rmk ,ISNULL((SELECT T0.[SYSDeb] - T0.[SYSCred] WHERE DateDiff(day, T0.DueDate, getdate())=0 and DateDiff(day, T0.DueDate,getdate())30 and DateDiff(day, T0.DueDate,getdate())60 and DateDiff(day, T0.DueDate,getdate())90 and DateDiff(day, T0.DueDate,getdate())=121),0) 'F' , t.DocDate AS LastPmtDate , t.[DocTotalSy] AS LastPmtAmt FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode Left outer join OCTG T2 On T1.GroupNum = T2.GroupNum LEFT JOIN (SELECT MAX(DocEntry) AS LastPmt, CardCode FROM ORCT GROUP BY CardCode) p ON T1.CardCode = p.CardCode LEFT JOIN ORCT t ON p.CardCode = t.CardCode AND p.LastPmt = t.DocEntry WHERE T1.CardType = 'C' and BalSccred + balscdeb <>0) T100 GROUP BY CARDCODE, CARDNAME, PymntGroup, Rmk, LastPmtDate, LastPmtAmt ORDER BY CARDCODE
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Danielle ,
The query what you have posted is not excuting,
Regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Danielle:
This should solve your problem:
SELECT
T1.CardCode AS "CardCode",
T1.CardName AS "CardName",
SUM(T0.BalScDeb - T0.BalScCred) AS "Balance",
SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) < 0 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "Future",
SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) BETWEEN 0 AND 30 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "0-30",
SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) BETWEEN 31 AND 60 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "31-60",
SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) BETWEEN 61 AND 90 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "61-90",
SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) BETWEEN 91 AND 120 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "91-120",
SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) > 120 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "121+"
FROM
JDT1 T0
INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
WHERE
BalScDeb <> BalScCred
GROUP BY
T1.CardCode, T1.CardName
Let me know...
Joerg.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.