avatar image
Former Member

Aging SQL server query

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"
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Mar 29, 2017 at 06:15 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

Skip to Content