Customer Ageing Query in Hana for SAP B1

[azacc]

Hi,

I'm trying to write a customer ageing query in SAP B1 Hana,

Here's what I have (lifted from SQL and converted):

It's getting stuck on the 'WHERE' in the IFNull lines if anyone can offer some assistance please?

SELECT
T1."CardCode", T1."CardName", T1."CreditLine", T0."RefDate", T0."Ref1" AS "Document Number",
CASE
WHEN T0."TransType" = 13 THEN 'Invoice'
WHEN T0."TransType" = 14 THEN 'Credit Note'
WHEN T0."TransType" = 30 THEN 'Journal'
WHEN T0."TransType" = 24 THEN 'Receipt'
END AS "Document Type",

T0."DueDate",
(T0."Debit" - T0."Credit") AS "Balance",

IFNULL((SELECT T0."Debit" - T0."Credit" WHERE DAYS_BETWEEN(T0."DueDate", '[%1]') <= -1), 0) AS "Future",
IFNULL((SELECT T0."Debit" - T0."Credit" WHERE DAYS_BETWEEN(T0."DueDate", '[%1]') >= 0 AND DAYS_BETWEEN(T0."DueDate", '[%1]') <= 30), 0) AS "Current",
IFNULL((SELECT T0."Debit" - T0."Credit" WHERE DAYS_BETWEEN(T0."DueDate", '[%1]') > 30 AND DAYS_BETWEEN(T0."DueDate", '[%1]') <= 60), 0) AS "31-60 Days",
IFNULL((SELECT T0."Debit" - T0."Credit" WHERE DAYS_BETWEEN(T0."DueDate", '[%1]') > 60 AND DAYS_BETWEEN(T0."DueDate", '[%1]') <= 90), 0) AS "61-90 Days",
IFNULL((SELECT T0."Debit" - T0."Credit" WHERE DAYS_BETWEEN(T0."DueDate", '[%1]') > 90 AND DAYS_BETWEEN(T0."DueDate", '[%1]') <= 120), 0) AS "91-120 Days",
IFNULL((SELECT T0."Debit" - T0."Credit" WHERE DAYS_BETWEEN(T0."DueDate", '[%1]') >= 121), 0) AS "121+ Days"

FROM "JDT1" T0
INNER JOIN "OCRD" T1 ON T0."ShortName" = T1."CardCode"

[ManishPant · Accepted solution]

Hello @azacc ,

Can you try this one?

SELECT
    T1."CardCode", 
    T1."CardName", 
    T1."CreditLine", 
    T0."RefDate", 
    T0."Ref1" AS "Document Number",
    CASE
        WHEN T0."TransType" = 13 THEN 'Invoice'
        WHEN T0."TransType" = 14 THEN 'Credit Note'
        WHEN T0."TransType" = 30 THEN 'Journal'
        WHEN T0."TransType" = 24 THEN 'Receipt'
    END AS "Document Type",
    T0."DueDate",
    (T0."Debit" - T0."Credit") AS "Balance",
    
    -- Calculate future balance
    IFNULL(
        CASE 
            WHEN DAYS_BETWEEN(T0."DueDate", '[%1]') <= -1 THEN T0."Debit" - T0."Credit" 
            ELSE 0 
        END, 
        0
    ) AS "Future",

    -- Calculate current balance
    IFNULL(
        CASE 
            WHEN DAYS_BETWEEN(T0."DueDate", '[%1]') >= 0 AND DAYS_BETWEEN(T0."DueDate", '[%1]') <= 30 THEN T0."Debit" - T0."Credit" 
            ELSE 0 
        END, 
        0
    ) AS "Current",

    -- Calculate 31-60 days balance
    IFNULL(
        CASE 
            WHEN DAYS_BETWEEN(T0."DueDate", '[%1]') > 30 AND DAYS_BETWEEN(T0."DueDate", '[%1]') <= 60 THEN T0."Debit" - T0."Credit" 
            ELSE 0 
        END, 
        0
    ) AS "31-60 Days",

    -- Calculate 61-90 days balance
    IFNULL(
        CASE 
            WHEN DAYS_BETWEEN(T0."DueDate", '[%1]') > 60 AND DAYS_BETWEEN(T0."DueDate", '[%1]') <= 90 THEN T0."Debit" - T0."Credit" 
            ELSE 0 
        END, 
        0
    ) AS "61-90 Days",

    -- Calculate 91-120 days balance
    IFNULL(
        CASE 
            WHEN DAYS_BETWEEN(T0."DueDate", '[%1]') > 90 AND DAYS_BETWEEN(T0."DueDate", '[%1]') <= 120 THEN T0."Debit" - T0."Credit" 
            ELSE 0 
        END, 
        0
    ) AS "91-120 Days",

    -- Calculate 121+ days balance
    IFNULL(
        CASE 
            WHEN DAYS_BETWEEN(T0."DueDate", '[%1]') >= 121 THEN T0."Debit" - T0."Credit" 
            ELSE 0 
        END, 
        0
    ) AS "121+ Days"

FROM 
    "JDT1" T0
INNER JOIN 
    "OCRD" T1 ON T0."ShortName" = T1."CardCode"

 Thanks.

[azacc · Accepted solution]

That looks like it works, thanks for your help!