Skip to Content
0

How to improve this SQL based formula calculation?

Oct 25, 2017 at 12:13 PM

26

avatar image
Former Member

I had a problem, which I already solved.

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)
SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

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
10 |10000 characters needed characters left characters exceeded