Skip to Content
0
Sep 02, 2017 at 07:34 PM

Customer Aging Report for HANA

943 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";