Aging SQL server query

[Former Member]

Hi,

I'm trying to use a query that I found in this community, but I get some wrong and missing values.. I'm using the SAP B1 9.2. Can anyone give me the most recent query?

SELECT T1."CardCode", T1."CardName", T1."CreditLine", 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."Debit"- T0."Credit"-COALESCE(T5."ReconSum",0)) "Balance"
   ,(CASE WHEN datediff(day,T0."DueDate", '20170131')>=0 AND datediff(day,T0."DueDate", '20170131')<=10 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "0-10 Dias"
   ,(CASE WHEN datediff(day,T0."DueDate", '20170131')>10 AND datediff(day,T0."DueDate", '20170131')<=20 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "11-20 Dias"
   ,(CASE WHEN datediff(day,T0."DueDate", '20170131')>20 AND datediff(day,T0."DueDate", '20170131')<=30 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "21-30 Dias"
   ,(CASE WHEN datediff(day,T0."DueDate", '20170131')>30 AND datediff(day,T0."DueDate", '20170131')<=40 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "31-40 Dias"
   ,(CASE WHEN datediff(day,T0."DueDate", '20170131')>40 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "41+ Days"
FROM JDT1 T0
INNER JOIN OCRD T1 ON T0."ShortName" = T1."CardCode"
INNER JOIN OJDT T2 ON T0."TransId" = T2."TransId"
LEFT JOIN (
 SELECT T4."ShortName", T4."TransId", SUM( T4."ReconSum" * CASE WHEN T4."IsCredit" = 'D' THEN 1 ELSE -1 END ) AS "ReconSum"
 FROM OITR T3
 INNER JOIN ITR1 T4 ON T3."ReconNum" = T4."ReconNum"
 WHERE T3."ReconDate" <= '20170131'
 GROUP BY T4."ShortName", T4."TransId"
) T5 ON T0."TransId" = T5."TransId" AND T0."ShortName" = T5."ShortName"
WHERE T0."RefDate" <= '20170131' AND T1."CardType" = 'C'
AND (T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0)) <> 0
ORDER BY T1."CardCode", T0."DueDate", T0."Ref1"

[Johan_H]

Hi Flávio,

There is no official aging query. This query was just written by someone, for a specific scenario.

If this gives you wrong and/or incomplete results, please show a screenshot of (part of) the results, and indicate what information is missing or wrong. If the information is wrong, please describe how it is wrong. If information is missing, please investigate that specific line, and describe what information you expected to be there.

Regards,

Johan