8
I have a table with the following columns:
id - id_manager - value - source - id_source
When registering an "Account to Pay", insert in this table managerial breakdowns of the company. Assuming that it would need to pay to a given supplier the value of 600.00 divided into 3 installments of 200.00, and this value would be dismembered to the following managerial sectors: "HR", "ADMINISTRATIVE" and "WAREHOUSE". My table would have the following values:
1 - 5 - 150,00 - P - 1
2 - 8 - 250,00 - P - 1
3 - 4 - 200,00 - P - 1
When I will make the discharge of the first installment, I make a division of management for the movement of accounts, and insert in the same table the following values:
4 - 5 - 50,00 - M - 1
5 - 8 - 83,33 - M - 1
6 - 4 - 66,67 - M - 1
That is, both the breakdowns of the registration of the main document (Bill to Pay), as the discharge, They are in the same table.
When the second installment is paid, the apportionment values will be the same cited above, because the value of the installment is the same.
Now when I pay off the third (last) installment, I can not do the appropriate apportionment because if done, the total sum of the rates may be different from the dismembered amount in the main document (Bill to Pay). To solve this, I need to make the sum of all managerial discharges and subtract from the main. So to make the final discharge, it is necessary to make two consultations. The first query will bring me the values when registered, ordered by code would be something like:
SELECT
id_gerencial,
valor
FROM
desmembramento
WHERE
origem = 'P'
ORDER BY
id;
This query returns to me:
5 - 150,00
8 - 250,00
4 - 200,00
To continue, I have a second consultation, which is the sum of the discharges grouped by management accounts:
SELECT
id_gerencial,
SUM(valor) AS soma
FROM
desmembramento
WHERE
origem = 'M'
GROUP BY
id_gerencial;
It turns out that this query for not having an ordering, does not return in the same order as when registered the main document, returning something of the type:
4 - 133,34
8 - 166,66
5 - 100,00
How do I make the second query come in the same order of the managers when they were registered?
I could have simplified the explanation more, but I was writing in my own way to better understand.
EDITED!
put a
order by id
at the end of the second select would not solve the sorting problem?– antunesleo_
Not because I use an aggregation function, to work I would have to put the id in "Group By" too, but for what I need I cannot add, it has to be grouped only by "managerial".
– Poliano Martini
Could you edit your question showing all the tables and their respective fields related to table dismemberment? And describe what this apportionment process would be? About order by, I’ve used it together with group by, and it’s really necessary to put the field in the grouping, but when I had to do this I didn’t have the query compromised.
– antunesleo_
I put a diagram to give a facilitated... the table that insert records for "Account to Pay" and "Account Movement" is the "dismemberment". The apportionment process is when I have an integral value and need to make a proportional division.
– Poliano Martini
The apportionment would be to track the records of all plots of that particular account?
– antunesleo_
No, the apportionment is: ((parcel_value / account_pay_value) * dismemberment_value)). If someone is interested in helping, add me to Skype so I can better explain what I do and how I need to order a query. Skype: polyanomartini
– Poliano Martini
Record the balance and the low value of each installment , if the balance is less than x cents zeroing the balance , a Rigger can do this.
– Motta