MySQL Forums
Forum List  »  MySQL Query Browser

Re: Calculations based on looping tables
Posted by: irek kordirko
Date: January 20, 2012 07:55PM

Hi,

if you want a pure SQL solution, you must go to Oracle, MS-SQL or Postgree-SQL databases.
They all support CTE (Common Table Expression - ANSII SQL-99 compliant syntax using WITH clause, called also hierarchical or recursive query).
Oracle has also it's own SQL syntax for hierarchical queries using CONNECT BY clause.
Neither MySql nor MS_Access have this facility, so you must write some procedural code to solve this.
MS-Access has built-in VBA support (Visual Basic for Application), if you prefer MS-Access then go to the MS-Access forums and post your question there.
Here is an simple example how to to do it in MySql (since we are on MySql forum, not MS-Access).
This code is only an example, don't use it on the production - it doesn't detect loops in data.
Struggling with hierarchical data you will face a problem of detecting a "loop" in data, which may cause an infinity loop.
A simple example of the loop would be (based on your "splitting" table) something like this:
Master,Base,SplitPercent
SP4, SP3, 50%
SP3, SP2, 50%
SP2, SP4, 50%
there is a loop in input data: SP4->SP3->SP2->SP4, this loop has to be detected, otherwise your code goes to the infinity loop and "hangs".
With CTE and CONNECT BY queries you get out of the box mechanism of detecting the "loop", but in the procedural code you must detect it on your own.


mysql> select * from todays_commissions;
+------+------------+
| name | commission |
+------+------------+
| SP1  |     100.00 |
| SP2  |     200.00 |
| SP3  |     300.00 |
| SP4  |     400.00 |
+------+------------+

mysql> select * from split;
+--------+------+--------------+
| master | base | splitPercent |
+--------+------+--------------+
| SP1    | SP1  |          100 |
| SP2    | SP2  |          100 |
| SP3    | SP1  |           50 |
| SP3    | SP2  |           50 |
| SP4    | SP3  |           75 |
| SP4    | SP1  |           25 |
+--------+------+--------------+

delimiter //
create procedure calculate_commissions()
/* Calculates commissions from 'todays_commissions' and 'split' tables
   Result is saved to 'commission' temporary table */
begin
    drop  table if exists commission;
    create temporary table if not exists commission(
        name varchar(10),
        commission numeric(10,2)
    ) STORAGE MEMORY;

    drop table if exists temp_commission;
    create temporary table if not exists temp_commission(
        name varchar(10),
        splitPercent int,
        commission numeric(10,2)
    ) STORAGE MEMORY;

    truncate commission;
    insert into commission ( name, commission )
    SELECT name,
           commission
    from todays_commissions;
    start_loop:
    LOOP
        truncate temp_commission;
        insert into temp_commission ( name, splitPercent, commission )
        select * from (
            select spl.base, spl.splitPercent, round( com.commission * spl.splitPercent / 100, 2 )
            from commission com
            join split spl on com.name = spl.master 
        ) tmp;
        truncate commission;

        IF NOT EXISTS( SELECT 1 FROM temp_commission WHERE splitPercent < 100 ) 
        THEN
            LEAVE start_loop; -- exit loop
        END IF;
        insert into commission ( name, commission)
        select name, commission 
        from temp_commission;
    END LOOP;
    insert into commission ( name, commission)
    select name, sum( commission )
    from temp_commission
    group by name;
end; /* calculate_commissions() */
//
delimiter ;

mysql> call calculate_commissions();
Query OK, 2 rows affected (0.49 sec)

mysql> select * from commission;
+------+------------+
| name | commission |
+------+------------+
| SP1  |     500.00 |
| SP2  |     500.00 |
+------+------------+
2 rows in set (0.00 sec)

Options: ReplyQuote


Subject
Written By
Posted
Re: Calculations based on looping tables
January 20, 2012 07:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.