Skip to Content
0
Aug 20, 2023 at 07:37 AM

display sum of lower level values to higher level in hana data

276 Views Last edit Aug 24, 2023 at 05:37 AM 2 rev

Hi all this is the query i am using

SELECT

m.solversionid,

m.variant,

m.description,

m.level,

m.services_crmid,

m.parentnode,

m.itemtype,

CASE

WHEN m.level = 1 THEN

COALESCE((

SELECT SUM(sub.sspmaxmargin)

FROM CV_FIN_FINAL_MIGRATION sub

WHERE sub.parentnode = m.parentnode AND sub.level = 2

), 0)

WHEN m.level = 0 AND m.description != 'Total' THEN

COALESCE((

SELECT SUM(sub.sspmaxmargin)

FROM CV_FIN_FINAL_MIGRATION sub

WHERE sub.parentnode = m.parentnode AND sub.level = 1

), 0)

WHEN m.level = 0 AND m.description = 'Total' THEN

COALESCE((

SELECT SUM(sub.sspmaxmargin)

FROM CV_FIN_FINAL_MIGRATION sub

WHERE sub.parentnode = m.parentnode

), 0)

ELSE

m.sspmaxmargin

END AS sspmaxmargin

FROM CV_FIN_FINAL_MIGRATION m

WHERE m.solversionid = '2371791525-055' AND m.variant = '0002'

ORDER BY m.level;

and the result i am getting is below i am seeing all 0 values for level 1 and 0 i should get sum of level 2 in level 1 and sum of level 1 in level 0

image.png

Attachments

image.png (124.3 kB)