09-05-2022 6:05 AM
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"
09-06-2022 11:27 AM
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.
09-07-2022 3:52 AM
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"
09-08-2022 4:50 PM