Customer Aging Report for HANA

Vinolan Pillay

Customer Aging Report for HANA

Sep 02, 2017 at 07:34 PM | 264 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";

SAP Business One, version for SAP HANA

Add comment

4 Comments

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

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

1 Answer

Sort by:

Votes

Newest

Oldest

Best Answer

This answer has been deleted.

This answer has been undeleted.

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
Add comment

10|10000 characters needed characters left characters exceeded
0 Comments
Comment