Skip to Content

Customer Aging Report for HANA

Hi,

I am trying to recreate the Customer Aging Report in HANA, as I am going to add columns for 150 and 180 days, but I am having difficulties with the date calculations.

Please see the code below:


SELECT OCRD."CardCode" AS "Customer Code", OCRD."CardName" AS "Customer Name", SUM(
    CASE 
        WHEN "SYSCred" <> 0 THEN "SYSCred" * -1 
        ELSE "SYSDeb" 
    END) AS "Balance Due", IFNULL(SUM(
    CASE 
        WHEN DAYS_BETWEEN(JDT1."DueDate", (SELECT NOW() "NOW" FROM DUMMY)) < 0 THEN 
        CASE 
            WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
            ELSE JDT1."BalDueDeb" 
        END 
    END), 0.00) AS "Future Remit", IFNULL(SUM(
    CASE 
        WHEN (DAYS_BETWEEN(JDT1."DueDate", (SELECT NOW() "NOW" FROM DUMMY)) >= 0 AND
         DAYS_BETWEEN(JDT1."DueDate", (SELECT NOW() "NOW" FROM DUMMY)) < 30) THEN 
        CASE 
            WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
            ELSE JDT1."BalDueDeb" 
        END 
    END), 0.00) AS "0-30 days", IFNULL(SUM(
    CASE 
        WHEN (DAYS_BETWEEN(JDT1."DueDate", (SELECT NOW() "NOW" FROM DUMMY)) >= 30 AND
         DAYS_BETWEEN(JDT1."DueDate", (SELECT NOW() "NOW" FROM DUMMY)) < 60) THEN 
        CASE 
            WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
            ELSE JDT1."BalDueDeb" 
        END 
    END), 0.00) AS "31 to 60 days", IFNULL(SUM(
    CASE 
        WHEN (DAYS_BETWEEN(JDT1."DueDate", (SELECT NOW() "NOW" FROM DUMMY)) >= 60 AND
         DAYS_BETWEEN(JDT1."DueDate", (SELECT NOW() "NOW" FROM DUMMY)) < 90) THEN 
        CASE 
            WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
            ELSE JDT1."BalDueDeb" 
        END 
    END), 0.00) AS "61 to 90 days", IFNULL(SUM(
    CASE 
        WHEN (DAYS_BETWEEN(JDT1."DueDate", (SELECT NOW() "NOW" FROM DUMMY)) >= 90 AND
         DAYS_BETWEEN(JDT1."DueDate", (SELECT NOW() "NOW" FROM DUMMY)) < 120) THEN 
        CASE 
            WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
            ELSE JDT1."BalDueDeb" 
        END 
    END), 0.00) AS "91 to 120 days", IFNULL(SUM(
    CASE 
        WHEN DAYS_BETWEEN(JDT1."DueDate", (SELECT NOW() "NOW" FROM DUMMY)) >= 120 THEN 
        CASE 
            WHEN "BalDueCred" <> 0 THEN "BalDueCred" * -1 
            ELSE "BalDueDeb" 
        END 
    END), 0.00) AS "120+ days" 
FROM JDT1, 
    OCRD 
WHERE JDT1."ShortName" = OCRD."CardCode" AND OCRD."CardType" = 'c' 
GROUP BY OCRD."CardCode", OCRD."CardName" HAVING SUM(
CASE 
    WHEN "SYSCred" <> 0 THEN "SYSCred" * -1 
    ELSE "SYSDeb" 
END) > 0 OR SUM(
CASE 
    WHEN "SYSCred" <> 0 THEN "SYSCred" * -1 
    ELSE "SYSDeb" 
END) < 0 
ORDER BY OCRD."CardCode";
Add a comment
10|10000 characters needed characters exceeded

  • Hi Lars,

    Thank you for the suggestion of using the CURRENT_DATE() option.

    The difficulties that I have is when I use the ADD_DAYS function. I accidentally omitted this from the code I initially posted.

    The error that I get is incorrect syntax near "(" Line 7 Column 44.

    Please see the revised code below:

    SELECT OCRD."CardCode" AS "Customer Code", OCRD."CardName" AS "Customer Name", SUM(
        CASE 
            WHEN "SYSCred" <> 0 THEN "SYSCred" * -1 
            ELSE "SYSDeb" 
        END) AS "Balance Due", IFNULL(SUM(
        CASE 
            WHEN DAYS_BETWEEN(JDT1."DueDate", ADD_DAYS('1900-01-01 00:00:00.000', CURRENT_DATE())) < 0 THEN 
            CASE 
                WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
                ELSE JDT1."BalDueDeb" 
            END 
        END), 0.00) AS "Future Remit", IFNULL(SUM(
        CASE 
            WHEN (DAYS_BETWEEN(JDT1."DueDate", ADD_DAYS('1900-01-01 00:00:00.000', CURRENT_DATE())) >= 0 AND
             DAYS_BETWEEN(JDT1."DueDate", ADD_DAYS('1900-01-01 00:00:00.000', CURRENT_DATE())) < 30) THEN 
            CASE 
                WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
                ELSE JDT1."BalDueDeb" 
            END 
        END), 0.00) AS "0-30 days", IFNULL(SUM(
        CASE 
            WHEN (DAYS_BETWEEN(JDT1."DueDate", ADD_DAYS('1900-01-01 00:00:00.000', CURRENT_DATE())) >= 30 AND
             DAYS_BETWEEN(JDT1."DueDate", ADD_DAYS('1900-01-01 00:00:00.000', CURRENT_DATE())) < 60) THEN 
            CASE 
                WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
                ELSE JDT1."BalDueDeb" 
            END 
        END), 0.00) AS "31 to 60 days", IFNULL(SUM(
        CASE 
            WHEN (DAYS_BETWEEN(JDT1."DueDate", ADD_DAYS('1900-01-01 00:00:00.000', CURRENT_DATE())) >= 60 AND
             DAYS_BETWEEN(JDT1."DueDate", ADD_DAYS('1900-01-01 00:00:00.000', CURRENT_DATE())) < 90) THEN 
            CASE 
                WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
                ELSE JDT1."BalDueDeb" 
            END 
        END), 0.00) AS "61 to 90 days", IFNULL(SUM(
        CASE 
            WHEN (DAYS_BETWEEN(JDT1."DueDate", ADD_DAYS('1900-01-01 00:00:00.000', CURRENT_DATE())) >= 90 AND
             DAYS_BETWEEN(JDT1."DueDate", ADD_DAYS('1900-01-01 00:00:00.000', CURRENT_DATE())) < 120) THEN 
            CASE 
                WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
                ELSE JDT1."BalDueDeb" 
            END 
        END), 0.00) AS "91 to 120 days", IFNULL(SUM(
        CASE 
            WHEN DAYS_BETWEEN(JDT1."DueDate", ADD_DAYS('1900-01-01 00:00:00.000', CURRENT_DATE())) >= 120 THEN 
            CASE 
                WHEN "BalDueCred" <> 0 THEN "BalDueCred" * -1 
                ELSE "BalDueDeb" 
            END 
        END), 0.00) AS "120+ days" 
    FROM JDT1, 
        OCRD 
    WHERE JDT1."ShortName" = OCRD."CardCode" AND OCRD."CardType" = 'c' 
    GROUP BY OCRD."CardCode", OCRD."CardName" HAVING SUM(
    CASE 
        WHEN "SYSCred" <> 0 THEN "SYSCred" * -1 
        ELSE "SYSDeb" 
    END) > 0 OR SUM(
    CASE 
        WHEN "SYSCred" <> 0 THEN "SYSCred" * -1 
        ELSE "SYSDeb" 
    END) < 0 
    
    
    
  • Alright, that was actually my mistake: the CURRENT_DATE function call goes without the brackets ().

    Anyhow, looking at the WHEN condition now it would be

    Days between "due_date" and a calculated date larger or equal 120.
    The calculated date should add X days to January 1st of 1900.
    And the X is supposed to come from CURRENT_DATE()... that doesn't make sense!

    Can you please verbally describe the rule you want to implement?

    What's the reference to the beginning of the last century about?
    Wouldn't you rather say something like:

    "if the due date is 120 days or more away from today then ..." ?

  • Hi Lars,

    The logic that you have provide me is correct and easier then what I started out with, however after making the changes, the query runs with no errors, but returns no lines.

    Basically, I am trying to re-create the Customer Recievables aging query so that I can include 150 days and 180+ days. I am trying to achieve this by converting a query that works in SQL, but I am still new to the HANA interface and the date functions involved in the HANA Language.

    Thank you for all your assistance in helping me so far.

    Please see the revised code below:

    SELECT OCRD."CardCode" AS "Customer Code", OCRD."CardName" AS "Customer Name", SUM(
        CASE 
            WHEN "SYSCred" <> 0 THEN "SYSCred" * -1 
            ELSE "SYSDeb" 
        END) AS "Balance Due", IFNULL(SUM(
    CASE 
            WHEN DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE) < 0 THEN 
            CASE 
                WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
                ELSE JDT1."BalDueDeb" 
            END 
        END), 0.00) AS "Future Remit", IFNULL(SUM(
        CASE 
            WHEN (DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE) >= 0 AND
             DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE) < 30) THEN 
            CASE 
                WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
                ELSE JDT1."BalDueDeb" 
            END 
        END), 0.00) AS "0-30 days", IFNULL(SUM(
        CASE 
            WHEN (DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE) >= 30 AND
             DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE) < 60) THEN 
            CASE 
                WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
                ELSE JDT1."BalDueDeb" 
            END 
        END), 0.00) AS "31 to 60 days", IFNULL(SUM(
        CASE 
            WHEN (DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE) >= 60 AND
             DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE) < 90) THEN 
            CASE 
                WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
                ELSE JDT1."BalDueDeb" 
            END 
        END), 0.00) AS "61 to 90 days", IFNULL(SUM(
        CASE 
            WHEN (DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE) >= 90 AND
             DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE) < 120) THEN 
            CASE 
                WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
                ELSE JDT1."BalDueDeb" 
            END 
        END), 0.00) AS "91 to 120 days", IFNULL(SUM(
        CASE 
            WHEN DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE) >= 120 THEN 
            CASE 
                WHEN "BalDueCred" <> 0 THEN "BalDueCred" * -1 
                ELSE "BalDueDeb" 
            END 
        END), 0.00) AS "120+ days" 
    FROM JDT1, 
        OCRD 
    WHERE JDT1."ShortName" = OCRD."CardCode" AND OCRD."CardType" = 'c' 
    GROUP BY OCRD."CardCode", OCRD."CardName" HAVING SUM(
    CASE 
        WHEN "SYSCred" <> 0 THEN "SYSCred" * -1 
        ELSE "SYSDeb" 
    END) > 0 OR SUM(
    CASE 
        WHEN "SYSCred" <> 0 THEN "SYSCred" * -1 
        ELSE "SYSDeb" 
    END) < 0 
    

    Kind regards,

    Vinolan Pillay

Related questions

1 Answer

  • Best Answer
    Posted on Sep 04, 2017 at 11:56 PM

    The statement in its current form is rather confusing as a lot of code is duplicated and it is not easy to see what the intention is.

    I rewrote it to this form:

    SELECT
          "Customer Code"
        , "Customer Name"
        , "Balance Due"
        , SUM(CASE 
                WHEN "DueDays" < 0 THEN "BalanceDue"
                ELSE 0.00
              END) AS "Future Remit"
        , SUM(CASE 
                WHEN "DueDays" >= 0 AND "DueDays" < 30 THEN "BalanceDue"
                ELSE 0.00
              END) AS "0-30 days"
        , SUM(CASE 
                WHEN "DueDays" >= 30 AND "DueDays" < 60 THEN "BalanceDue"
                ELSE 0.00
              END) AS "31 to 60 days" 
        , SUM(CASE 
                WHEN "DueDays" >= 60 AND "DueDays" < 90 THEN "BalanceDue"
                ELSE 0.00
              END) AS "61 to 90 days"
        , SUM(CASE 
                WHEN "DueDays" >= 90 AND "DueDays" < 120 THEN "BalanceDue"
                ELSE 0.00
              END) AS "91 to 120 days" 
        , SUM(CASE 
                WHEN "DueDays" >= 120 THEN "BalanceDue"
                ELSE 0.00
              END) AS "120+ days" 
    FROM (SELECT 
              OCRD."CardCode" AS "Customer Code"
            , OCRD."CardName" AS "Customer Name"
            , DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE) as "DueDays"
            , CASE 
               WHEN JDT1."BalDueCred" <> 0 THEN JDT1."BalDueCred" * -1 
               ELSE JDT1."BalDueDeb" 
              END AS "BalanceDue"
        FROM 
                       JDT1 
            INNER JOIN OCRD 
                    ON JDT1."ShortName" = OCRD."CardCode" 
                   AND OCRD."CardType" = 'c' 
        GROUP BY 
              OCRD."CardCode"
            , OCRD."CardName" 
        HAVING 
            SUM(CASE 
                WHEN "SYSCred" <> 0 THEN "SYSCred" * -1 
                ELSE "SYSDeb" 
            END) <> 0 );

    What I did was:

    • make inner join and join conditions explicit
    • moved "due days" calculation and "balance due" calculation into a sub-query
    • changed OR branch in HAVING clause from "<0 OR >0" to "<>0"
    • moved column mapping CASE statements to outer SELECT
    • provided ELSE clause to ALWAYS return a double (0.00 or "balance due") - this allowed to get rid of the IFNULL() function

    Now, the statement is a lot easier to read and should actually run quicker, too.

    Based on what I understand now, the lack of result rows could be caused by:

    • non-matching join
      or
    • the HAVING clause filtering out all results
      or
    • at least one of the tables is empty

    It should now be easy to check these possible causes one by one.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.