Vinolan Pillay

Customer Aging Report for HANA

Posted on Sep 02, 2017 at 07:34 PM 603 Views

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

4 Comments

Vinolan Pillay

Sep 03, 2017 at 09:37 AM

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

Lars Breddemann Vinolan Pillay

Sep 04, 2017 at 07:31 AM

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 ..." ?
- Like 0
- Share
- Alert Moderator

Vinolan Pillay

Sep 04, 2017 at 08:03 PM

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

1 Answer

Sort by:

Votes

Newest

Oldest

Best Answer

This answer has been deleted.

This answer has been undeleted.

Lars Breddemann

Posted on Sep 04, 2017 at 11:56 PM

0
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
- Alert Moderator
- Share
Add a comment
10|10000 characters needed characters left characters exceeded
0 Comments
Comment on This Answer

Comment

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.

Rules of Engagement

Know someone who can answer? Share a link to this question.

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

Customer Aging Report for HANA

Add a comment

Assigned Tags

Related questions

1 Answer

Add a comment

Before answering