Repeating rows of date intervals Between dates Firebird

datefirebirdinterval

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?

Best Answer

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

Related Solutions

The difference between Interbase and Firebird

InterBase version 6 was released under an open-source license in mid-2000, and was immediately forked to become the Firebird project.

Later versions of InterBase reverted to closed-source and the projects have diverged. Both are still actively developed. Developer’s tools and libraries traditionally support both servers but they are growing apart.

Firebird – Calculate Time Interval Between Two Dates

Relatively green Firebird n00b here w/a fair PostgreSQL background, although specialized GIS work focused mostly to DB design and the sourcing/cleaning/making spatial multi-source data sets (usually of dubious quality) and then querying for map-able results in answer to specific location riddles. Toolbox is light, therefore, on Admin/Optimization/3rd Party Application Setup etc. You may (err, will) see some Firebird specific questions from me in the coming weeks. Thanks in advance! Took a stab at this slightly older Firebird question to get a feel for the new-to-me flavor of date handling and concatenation. Took more tweaking & testing than expected & my dabbling may not be the most efficient/elegant solution but, assuming the existence of column join_date in table employees, this will work:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
THEN datediff(year, join_date, current_date)-1
ELSE datediff(year, join_date, current_date)
END as Y,
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)-1
WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)>=0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)
WHEN datediff(year, join_date, current_date) = datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

The subquery turned conditional on me when it became clear that: a) calculations on year & month don't consider the date as a whole; and b) date subtraction will happily return negative values regardless of the order of events.

Year/Month/Day Example: datediff() returns a value of 1 when retrieving the number of days, months, or years between 31.12.2015 and 1.1.2016.

Negative Value Example: about 69% of my test-date values are less than zero when I calculate the number of days less the number of days contained by whole months using:

SELECT datediff(day, dateadd(month, datediff(month, date_column, current_date), date_column), current_date) as D FROM date_table

We can streamline the subquery if substituting 365 days for one year is acceptable. The trick works with my test data but may trigger pitfalls with a broader range of dates. The simplified query looks like:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
datediff(day, join_date, current_date)/365 as Y,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

Holler if I goofed it or if I employed bad/overwrought logic. Hope it's helpful to someone at some point! Keep a lookout for new Firebird questions (might already have locked in struggle with a JDBC connection string that just will not lose).

Thanks and hello again! --Rob

I never had a plan-- Anonyman

Best Answer

Related Solutions

The difference between Interbase and Firebird

Firebird – Calculate Time Interval Between Two Dates

Related Question