0

# How to improve this SQL based formula calculation?

Oct 25, 2017 at 12:13 PM

26

Former Member

But I feel it can be solved in a more elegant way and I am sure there are much more skilled members in this forum, who may give me a hint.

I created a parameter in a planning function, which provided my Hana procedure with all needed data in a data set. No modification of the data is needed to calculate the results.

Anyway I had trouble to extract the data, so I splitted it up in a lot of tables and calcuated my values using JOINs.

The problem:

I have a table which contains the sales shares for a whole month, and also for the weeks within this month (which always sums up to 100% = the monthly share).

The formula for my weekly results is:

(weekly share 1 Product 1 * corresponding monthly share + weekly share 1 Product 2 * corresponding monthly share / (addition of all monthly shares (Product 1 and Product 2)

Would you solve this with nested SELECTs instead?

Or was my approach not so bad in the end?

problem.jpg (49.8 kB)

ERALPER YILMAZ Oct 25, 2017 at 02:46 PM
0

Hello Oliver,

Actually, I tried to create the query for your case as follows

```select
distinct
month, WeekNo,
week_value / week_quantity as week_performance
from (

select
m.month,
w.WeekNo,
p.productid,
p.percentage,
m.quantity,
p.percentage * m.quantity as value,
sum(p.percentage * m.quantity) over (partition by w.WeekNo) as week_value,
sum(m.quantity) over (partition by w.WeekNo) as week_quantity
from (
select
distinct week as WeekNo
from WEEKLY_SALES_AS_PERCENTAGE
) as w
left outer join WEEKLY_SALES_AS_PERCENTAGE p
on p.week = w.WeekNo
left outer join MONTHLY_SALES m
on m.month = p.month
and m.productid = p.productid

) as calc;```

And the output is as follows

I'm not sure if this was what you have asked for but I found it useful for aggregation functions likes SUM(), Count(), and window functions like Row_Number() with Partition By clause

sql.png (3.3 kB)
Share