Skip to Content
0
Jun 18, 2020 at 03:43 AM

SUM and JOIN two queries SAP B1 HANA

789 Views Last edit Jun 18, 2020 at 04:23 AM 2 rev

I have 2 query who calculate the total amount for Direct and Indirect distribution rule amount related to a cost center/cost profit .

In this exemple , i took one cost profit center "C -NOKIA"

1 ) The Sum of Direct D.R Amount query :

SELECT(sum(T0."Credit")- sum(T0."Debit"))As"Total", T0."ProfitCode"FROM JDT1 T0 WHERE T0."ProfitCode"ISNOTNULLAND T0."ProfitCode"='C -NOKIA'GROUPBY T0."ProfitCode"

result :

2 ) The Sum of Indirect D.R amount query :

SELECT sum(T3."PrcAmount")As"Total", IFNULL(T2."PrcCode", T3."PrcCode")AS"ProfitCode"FROM OJDT T0 INNERJOIN JDT1 T1 ON T0."TransId"= T1."TransId"LEFTJOIN OCR1 T2 ON T2."OcrCode"= T1."ProfitCode"LEFTJOIN MDR1 T3 ON T3."OcrCode"= T1."ProfitCode"WHERE IFNULL(T1."ProfitCode",'')<>''AND IFNULL(T2."PrcCode", T3."PrcCode")='C -NOKIA'GROUPBY  IFNULL(T2."PrcCode", T3."PrcCode"), T3."PrcAmount"

Result :

Now i want to sum both total in a single query , to show me a result like this (from the official Cost center report in SAP B1 )

Can you please help ?

Thanks , i need this query for a particular dashboard .

Attachments

code-1.png (44.3 kB)
c-nokia-45.png (53.0 kB)
total-89.png (27.8 kB)