1
I need to make a query in the database where the records are not yet saved. I will explain better:
In a system that I am developing, the user can register a recurring expense, that is, it will be repeated every month/day (according to the user’s choice). With this I create a job that will create an expense (will create a record in the table despesas) on the set date and schedule the next one to be created.
Such recurrent expenditure may have a predetermined date for closure or may be forever.
What I need is to do a consultation that will add up the expenses per month, example:
table recorrencia
And yet I have the table where the expenses are:
table despesas
Let’s say today was day 01/12/2015 and would like to know the value of my expenses until day 30/04/2015. I need you to return the following:
That is, it calculates when a recurring expense will generate an expense, join with the expense table and do the calculation.
How could I do this query by sql? with a view or something like that?
I am using the postgresql database with the Ruby on Rails programming language.
Thank you
He thought of creating the record with a "flag" of the foreseen/realized type , instead of "job" a "Trigger" in the table of RECURSION povoaria the EXPENSES, a sql would be simple treating the "flag".
– Motta
Another simpler solution would be to use only a table called expenses, placing a field called
tipo CHAR(1)that will identify the type of this expense. So it would be much simpler to select and sum the expenses.– antunesleo_
@Motta, the problem is that I can’t use this flag, because think, I have an expense (as is the case with the rent of the example above) that does not have a determined date to finish. If the client makes a query to know what is his estimated expenses for 6 months from now should bring this rent, and so I can not enter records in the table
despesas, because I don’t have a deadline.– Bmucelini
@antunesleo_ thanks for the tip. In fact I do 2 ways, 1 exactly as you said (for case of expenses and revenues), but also need to do otherwise where I have sales contracts, which will generate a sale and later a release in the table of
financas(that in my example I put as expenses to simplify the explanation).– Bmucelini
@Bmucelini , my idea is to create the previous record and signal in some way that is previous , when effecting just change the flag , cashflow can go in this record directly.
– Motta
@Motta, the problem is that there is no way I can create this previous record, because depending on the recurrence configuration it is impossible for me to create a previous record, as it may be a recurrence forever I do not have a deadline to register all expenses previously with an unpaid flag. I’m sorry if that’s not what you meant, but if I got it wrong, could you explain your position better? cite an example so that you can understand more clearly =). Thank you
– Bmucelini
@Bmucelini , I understood another solution would be a view or Procedure returning a course , assembling a planned expense line based on the table "recurrence", the idea is a little rough but would be this
– Motta