Can you give us a little background on what it is you're trying to do?
Iīve got this table that stores geological data types, and the amounts purchased by users. But in order to bill the guys, I have to apply a lot of strange rules: Some data types belong to the same group, which has a maximum number with which the user can purchase without paying, like an account, if he blows it, he has to pay for the extra. Within a group of data types, thereīs an order between the types that commands which will enter first in the line, and the excedents will be charged. The big problem is that some types are charged in unities, and others are floating numbers -I know... Itīs crazy...):
Imgine a linked list in which if the unities passes a certain number, those who passit shall be charged.
is it that the qtd_data is broken into 1's and the last fraction amount such that the sum is equal to its value?
might not be the best solution, but read the data in a cursor then start a loop that will subrtract 1 from qtd_data until it is less than 1 and during this time it will update a table with cod_data and 1, atlast with cod_data and the fraction left.
cod_grupo is the group in which that type belongs, num_ordem is the order in which it should place itself on the list, and cod_dado is the data type.
Letīs suppose the limit is 4. Looking at tsvd_vol_dados, I have 5.5 data total, so I have to charge 1.5 right? Well... Not exactly... Since type 5 is measured in unities (not in Kbīs as type 2), I canīt charge for half unity, so in this case Iīll charge for 2 types 5, that will leave the user with 0.5 in his account to acquire later.
I already have tried some of the solutions described here, but Iīm trying to solve it with one query only.
So this were my thoughts:
FROM (SUPER-QUERY THAT DOES DEVIDE INTO UNITIES)
WHERE ROWNUMBER > LIMIT.
Itīs really hard to explain, I hope I did better this time. Anyway, thank all for your time.