SAP HANA Query Help

[Former Member]

Hi,

I am having trouble with a HANA query I wrote to look at sales and GP by customer by month (pasted below). The problem is that I want the query to only consider sales for the current year, but when I have the following in it - WHERE YEAR(T0."DocDate") = YEAR(CURRENT_DATE), it seems to limit the results to only customers that have an invoice or credit posted this year, whereas, I need a full list of customers because I need to see where sales or GP is zero for example.

I think trying to add year in with this line might help -

SUM(CASE(MONTH(T0."DocDate")) WHEN '1' THEN T1."LineTotal" END) AS "Jan Sales",

- but I have no idea how to get both the month and year in here, i.e. Jan 2017.

If anyone is able to help or give me any pointers, that would be great.

Thanks,

Ali

SELECT T0."CardCode", T0."CardName", T2."U_BTotal",

SUM(CASE(MONTH(T0."DocDate")) WHEN '1' THEN T1."LineTotal" END) AS "Jan Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '2' THEN T1."LineTotal" END) AS "Feb Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '3' THEN T1."LineTotal" END) AS "Mar Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '4' THEN T1."LineTotal" END) AS "Apr Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '5' THEN T1."LineTotal" END) AS "May Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '6' THEN T1."LineTotal" END) AS "Jun Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '7' THEN T1."LineTotal" END) AS "Jul Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '8' THEN T1."LineTotal" END) AS "Aug Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '9' THEN T1."LineTotal" END) AS "Sep Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '10' THEN T1."LineTotal" END) AS "Oct Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '11' THEN T1."LineTotal" END) AS "Nov Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '12' THEN T1."LineTotal" END) AS "Dec Sales", 

SUM(CASE(MONTH(T0."DocDate")) WHEN '1' THEN T1."GrssProfit" END) AS "Jan GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '2' THEN T1."GrssProfit" END) AS "Feb GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '3' THEN T1."GrssProfit" END) AS "Mar GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '4' THEN T1."GrssProfit" END) AS "Apr GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '5' THEN T1."GrssProfit" END) AS "May GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '6' THEN T1."GrssProfit" END) AS "Jun GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '7' THEN T1."GrssProfit" END) AS "Jul GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '8' THEN T1."GrssProfit" END) AS "Aug GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '9' THEN T1."GrssProfit" END) AS "Sep GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '10' THEN T1."GrssProfit" END) AS "Oct GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '11' THEN T1."GrssProfit" END) AS "Nov GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '12' THEN T1."GrssProfit" END) AS "Dec GP",

FROM OINV T0  INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" 
LEFT JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"
INNER JOIN OSLP T3 ON T2."SlpCode" = T3."SlpCode"

WHERE T0."CANCELED" = 'N' AND T1."LineType" = 'R' AND T1."TaxOnly" = 'N' AND T1."Quantity" >= 0 AND YEAR(T0."DocDate") = YEAR(CURRENT_DATE)

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

UNION ALL

SELECT T0."CardCode", T0."CardName", T2."U_BTotal",

SUM(CASE(MONTH(T0."DocDate")) WHEN '1' THEN (T1."LineTotal"*-1) END) AS "Jan Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '2' THEN (T1."LineTotal"*-1) END) AS "Feb Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '3' THEN (T1."LineTotal"*-1) END) AS "Mar Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '4' THEN (T1."LineTotal"*-1) END) AS "Apr Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '5' THEN (T1."LineTotal"*-1) END) AS "May Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '6' THEN (T1."LineTotal"*-1) END) AS "Jun Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '7' THEN (T1."LineTotal"*-1) END) AS "Jul Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '8' THEN (T1."LineTotal"*-1) END) AS "Aug Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '9' THEN (T1."LineTotal"*-1) END) AS "Sep Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '10' THEN (T1."LineTotal"*-1) END) AS "Oct Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '11' THEN (T1."LineTotal"*-1) END) AS "Nov Sales", 
SUM(CASE(MONTH(T0."DocDate")) WHEN '12' THEN (T1."LineTotal"*-1) END) AS "Dec Sales", 

SUM(CASE(MONTH(T0."DocDate")) WHEN '1' THEN (T1."GrssProfit"*-1) END) AS "Jan GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '2' THEN (T1."GrssProfit"*-1) END) AS "Feb GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '3' THEN (T1."GrssProfit"*-1) END) AS "Mar GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '4' THEN (T1."GrssProfit"*-1) END) AS "Apr GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '5' THEN (T1."GrssProfit"*-1) END) AS "May GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '6' THEN (T1."GrssProfit"*-1) END) AS "Jun GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '7' THEN (T1."GrssProfit"*-1) END) AS "Jul GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '8' THEN (T1."GrssProfit"*-1) END) AS "Aug GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '9' THEN (T1."GrssProfit"*-1) END) AS "Sep GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '10' THEN (T1."GrssProfit"*-1) END) AS "Oct GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '11' THEN (T1."GrssProfit"*-1) END) AS "Nov GP",
SUM(CASE(MONTH(T0."DocDate")) WHEN '12' THEN (T1."GrssProfit"*-1) END) AS "Dec GP",

FROM ORIN T0  INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry" 
LEFT JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"
INNER JOIN OSLP T3 ON T2."SlpCode" = T3."SlpCode"

WHERE T0."CANCELED" = 'N' AND T1."LineType" = 'R' AND T1."TaxOnly" = 'N' AND T1."Quantity" >= 0 AND YEAR(T0."DocDate") = YEAR(CURRENT_DATE)

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

ORDER BY T0."CardCode" 

[former_member228588 · Accepted solution]

Hi Ali.

Look if you have generated the time dimension data in your HANA instance in table "_SYS_BI"."M_TIME_DIMENSION". IF not please generate time dimension data on date granularity . Once this is done , you can join on this table on date, to get column like CALMONTH. This will resolve your issue.

[Former Member]

Thanks Mrityunjay. I will check this out.