Skip to Content
0

Customer Aging Report for HANA

Sep 02, 2017 at 07:34 PM

172

avatar image

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";
10 |10000 characters needed characters left characters exceeded

And what are the difficulties you mention? Do you get errors? If so, which ones?

As a general tip: you don't need sub-selects to use the now() function.

Instead of

DAYS_BETWEEN(JDT1."DueDate", (SELECT NOW() "NOW" FROM DUMMY)) >= 1

you can just write

DAYS_BETWEEN(JDT1."DueDate", now() ) >= 1

Even better would be, if you use current_date(), instead of now(), since this provides the data type and it is easier to understand what it does:

DAYS_BETWEEN(JDT1."DueDate", CURRENT_DATE()) >= 1
0

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 



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

0

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

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Lars Breddemann
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.

Share
10 |10000 characters needed characters left characters exceeded