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

  • 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

  • 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 ..." ?

  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 04, 2017 at 11:56 PM
    -1

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