SAL HANA SQL Query question

[former_member820000]

HI all

i have code for Some Monthly sales analysis Report.

i seen this on

Useful SAP Business One Queries: Comparing Monthly Sales Across Multiple Years - ASUG

but some point reason this code is not working

seems getdata is strange

do you guys and ladies know how to fix it?

SELECT T0."CardCode", T0."CardName",

(SUM(T1."Debit") - sum(T1."Credit")) AS "2017",

((SUM(T1."Debit") - sum(T1."Credit"))/12) AS "2017Average Sales",

(SUM(T2."Debit") - sum(T2."Credit")) AS "2018",

((SUM(T2."Debit") - sum(T2."Credit"))/Month(GETDATE()))AS "2018 Average Sales"

FROM OCRD T0

LEFT JOIN JDT1 T1

ON T1."ShortName" = T0."CardCode"

AND YEAR(T1."DueDate") =2017 AND T1."TransType" in('13','14')

LEFT JOIN JDT1 T2 ON T2."ShortName" = T0."CardCode" AND Year(T2."DueDate") = 2018

AND T2."TransType" in ('13','14')

WHERE T0."CardType" = 'C'

GROUP BY T0."CardCode", T0."CardName"

[Vitaliy-R]

I understand you have an SAP HANA database. An example you share is for the MS SQL Server database (as stated as well in the article you linked).

If I got it right then the equivalent of GetDate from MS SQL should be CURRENT_DATE in HANA.

Regards.

[former_member820000]

I tried but seems still error

i'm really beginner so i don't know how to solve this

SELECT T0."CardCode", T0."CardName",

(SUM(T1."Debit") - sum(T1."Credit")) AS "2017",

((SUM(T1."Debit") - sum(T1."Credit"))/12) AS "2017Average Sales",

(SUM(T2."Debit") - sum(T2."Credit")) AS "2018",

((SUM(T2."Debit") - sum(T2."Credit"))/Month(CURRENT_DATE())) AS "2018 Average Sales"

FROM

OCRD T0

LEFT JOIN JDT1 T1

ON T1."ShortName" = T0."CardCode"

AND YEAR(T1."DueDate") =2017 AND T1."TransType" in('13','14')

LEFT JOIN JDT1 T2 ON T2."ShortName" = T0."CardCode" AND Year(T2."DueDate") = 2018

AND T2."TransType" in ('13','14') WHERE T0."CardType" = 'C' GROUP BY T0."CardCode", T0."CardName"

[Vitaliy-R]

CURRENT_DATE is a function, but it does not require () at the end. Try that.