MySQL calculating query

Question

I have this table, only two columns, each record stores an interest rate for a given month:

id     rate
===========
199502 3.63
199503 2.60
199504 4.26
199505 4.25
...    ...
201704 0.79
201705 0.93
201706 0.81
201707 0.80
201708 0.14

Based on this rates, I need to create another table of accumulated rates which similar structure, whose data is calculated as function of a YYYYMM (month/year) parameter, this way (this formula is legally mandatory):

The month given as parameter has always rate of 0 (zero)
The month immediately previous has always a rate of 1 (one)
The previous months' rates will be (one) plus the sum of rates of months between that given month and the month given as parameter.

I'll clarify this rules with this example, given parameter 201708:

SOURCE         CALCULATED
id     rate    id       rate
===========    =============
199502 3.63    199502 360.97 (1 + sum(rate(199503) to rate(201707)))
199503 2.60    199503 358.37 (1 + sum(rate(199504) to rate(201707)))
199504 4.26    199504 354.11 (1 + sum(rate(199505) to rate(201707)))
199505 4.25    199505 349.86 (1 + sum(rate(199506) to rate(201707)))
...    ...     ...    ...
201704 0.79    201704   3.54 (1 + rate(201705) + rate(201706) + rate(201707))
201705 0.93    201705   2.61 (1 + rate(201706) + rate(201707))
201706 0.81    201706   1.80 (1 + rate(201707))
201707 0.80    201707   1.00 (per definition)
201708 0.14    201708   0.00 (per definition)

Now I've already implemented a VB.NET function that reads the source table and generates the calculated table, but this is done in runtime at each client machine:

Public Function AccumRates(targetDate As Date) As DataTable
    Dim dtTarget = Rates.Clone
    Dim targetId = targetDate.ToString("yyyyMM")
    Dim targetIdAnt = targetDate.AddMonths(-1).ToString("yyyyMM")
    For Each dr In Rates.Select("id<=" & targetId & " and id>199412")
        If dr("id") = targetId Then
            dtTarget.Rows.Add(dr("id"), 0)
        ElseIf dr("id") = targetIdAnt Then
            dtTarget.Rows.Add(dr("id"), 1)
        Else
            Dim intermediates =
                Rates.Select("id>" & dr("id") & " and id<" & targetId).Select(
                    Function(ldr) New With {
                        .id = ldr.Field(Of Integer)("id"),
                        .rate = ldr.Field(Of Decimal)("rate")}
                    ).ToArray
            dtTarget.Rows.Add(
                dr("id"),
                1 + intermediates.Sum(
                    Function(i) i.rate))
        End If
    Next
    Return dtTarget
End Function

My question is how can I put this as a query in my database so it can be used dynamically by other queries which would use these accumulated rates to update debts to any given date.

Thank you very much!

EDIT

I managed to make a query that returns the data I want, now I just don't know how to encapsulate it so that it can be called from another query passing any id as argument (here I did it using a SET ... statement):

SET @targetId=201708;
SELECT 
    id AS id_acum,
    COALESCE(1 + (SELECT 
                    SUM(taxa)
                FROM
                    tableSelic AS ts
                WHERE
                    id > id_acum AND id < @targetId
                LIMIT 1),
            IF(id >= @targetId, 0, 1)) AS acum
FROM
    tableSelic
    WHERE id>199412;

That's because I'm pretty new to MySQL, I'm used to MS-Access where parametrized queries are very straightfoward to create.

given parameter 201708 .. why 201706 ratio isnt 1.94? `1 + 0.80 + 0.14` — Juan Carlos Oropeza, Aug 07 '17 at 15:58
Current month isn't complete and its rate doesn't count for accumulated rates. Legal stuff. — VBobCat, Aug 07 '17 at 16:00
Ok, then rule 3 should be written different to make it clear doesnt include the parameter. — Juan Carlos Oropeza, Aug 07 '17 at 16:01
It is already excluded by the Select scope upon iteration... — VBobCat, Aug 07 '17 at 16:03
you need create a recursive query. here is an example https://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql — Juan Carlos Oropeza, Aug 07 '17 at 16:03
Basically is just a loop and you keep inserting values on a temporal table, then return the temporal table. — Juan Carlos Oropeza, Aug 07 '17 at 16:06

score 1 · Answer 1 · answered Aug 07 '17 at 17:01

For example:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL PRIMARY KEY
,rate DECIMAL(5,2) NOT NULL
);

INSERT INTO my_table VALUES
(201704,0.79),
(201705,0.93),
(201706,0.81),
(201707,0.80),
(201708,0.14);

SELECT *
     , CASE WHEN @flag IS NULL THEN @i:=1 ELSE @i:=@i+rate END i
     , @flag:=1 flag  
  FROM my_table
     , (SELECT @flag:=null,@i:=0) vars 
 ORDER 
    BY id DESC;
+--------+------+-------------+-------+------+------+
| id     | rate | @flag:=null | @i:=0 | i    | flag |
+--------+------+-------------+-------+------+------+
| 201708 | 0.14 | NULL        |     0 |    1 |    1 |
| 201707 | 0.80 | NULL        |     0 | 1.80 |    1 |
| 201706 | 0.81 | NULL        |     0 | 2.61 |    1 |
| 201705 | 0.93 | NULL        |     0 | 3.54 |    1 |
| 201704 | 0.79 | NULL        |     0 | 4.33 |    1 |
+--------+------+-------------+-------+------+------+
5 rows in set (0.00 sec)

score 0 · Accepted Answer · answered Aug 08 '17 at 13:51

Ok, I made it with a function:

CREATE FUNCTION `AccumulatedRates`(start_id integer, target_id integer) RETURNS decimal(6,2)
BEGIN
    DECLARE select_var decimal(6,2);
    SET select_var = (
        SELECT COALESCE(1 + (
            SELECT SUM(rate) 
            FROM tableRates 
            WHERE id > start_id AND id < target_id LIMIT 1
            ), IF(id >= unto, 0, 1)) AS acum 
        FROM tableRates 
        WHERE id=start_id);
    RETURN select_var;
END

And them a simple query:

SELECT *, AccumulatedRates(id,@present_id) as acum FROM tableRates;

where @present_id is passed as parameter.

Thanks to all, anyway!

MySQL calculating query

2 Answers2