Skip to Content
avatar image
Former Member

How to improve this SQL based formula calculation?

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 25, 2017 at 02:46 PM

    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

    Add comment
    10|10000 characters needed characters exceeded