Skip to Content
0
Aug 11, 2016 at 02:43 PM

Can I add aging date to my AR Aging query with remarks? I want to run as of 7/31/2016

19 Views

SELECT T1.CardCode AS "CardCode", T1.CardName AS "CardName", T3.[PymntGroup] as Terms, T1.[ProjectCod] as "Code",T2.[PrjName] as " AR Collections Status" , 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()) > 91 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "91+", t.DocDate AS LastPmtDate , t.[DocTotalSy] AS LastPmtAmt, T1.[AddID] as 'Last Call date/Initials',CAST(T1. [Free_Text] as varchar(2000)) as Rmk FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode Left OUter JOIN OPRJ T2 ON T1.ProjectCod = T2.PrjCode Left outer join OCTG T3 On T1.GroupNum = T3.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 LEFT JOIN (SELECT SUM(DocTotalsy) AS TotalPmt, CardCode, DocDate FROM ORCT GROUP BY CardCode, DocDate) pt ON t.CardCode = pt.CardCode AND t.DocDate = pt.DocDate WHERE BalScDeb <> BalScCred and T1.Cardtype='C' GROUP BY T1.CardCode, T1.CardName, T3.[PymntGroup], T1.[AddID] ,Cast ( T1. [Free_Text] as varchar(2000)), t.DocDate,T1.[ProjectCod], T2.[PrjName],t.DocDate, t.[DocTotalSy] Order by '91+' DESC