Repeating rows of date intervals Between dates Firebird

Question

I am stuck in a case that on my financial sys, I need to add a option for value to be monthly, weekly or every 2 weeks

example:

DESC    |   VALUE |    DATEADD |  FREQUENCY
--------+---------+------------+-----------
SALARY  | 1000,00 | 26/03/2017 |         30 (MONTHLY)
DINNER  |   50,00 | 26/03/2017 |          7 (WEEKLY)
BUSTICK |   75,00 | 26/03/2017 |         15 


select 
    * 
from 
    tableexpensives 
where 
    datesys between '01/01/2017' and '31/12/2017'

so it should show on sql

APR | SALARY   | 1000,00 | (1x month)
APR | DINNER   |  200,00 | (4x month)
APR | BUSTICK  |  150,00 | (2X month)
MAI | SALARY   | 1000,00 | (1x month)
MAI | DINNER   |  200,00 | (4x month)
MAI | BUSTICK  |  150,00 | (2X month)
....

Any idea how to do that?

Answer 1

Your question is not 100% clear, and I'm proposing a tentative answer using PostgreSQL (I don't have FireBird installed at the moment). The concepts are the same regardless of database make.

1st: I would represent data in an easier way:

CREATE TABLE expenses
(
    description varchar, 
    amount numeric(10,2), 
    dateadd date, 
    times_per_month numeric(10, 0)
) ;

INSERT INTO expenses
    (description, amount, dateadd, times_per_month)
VALUES
    ('SALARY',    1000.00, '2017-03-26', 1),
    ('DINNER',      50.00, '2017-03-26', 4),
    ('BUSTICK',     75.00, '2017-03-26', 2) ;

From this point on, and assuming you would like this data to appear under the heading MARCH (and not APRIL, MAY, ... forever), you would just use something very simple: a multiplication.

SELECT
  to_char(dateadd, 'MON') AS month,
  description,
  (amount * times_per_month) AS subtotal
FROM
  expenses ;

And you'd get:

| month | description | subtotal |
|-------|-------------|----------|
|   MAR |      SALARY |     1000 |
|   MAR |      DINNER |      200 |
|   MAR |     BUSTICK |      150 |

Check it at: http://sqlfiddle.com/#!15/34efd/1/0