1. Registered User
Join Date
Oct 2003
Posts
6

OK, now this will require a super-guru!

Supose I have a table with columns: cod_data, qtd_data.

Now supose I had this values in first row: 1 - 1.6
And in second row : 2 - 2

Can anyone tell me how to build a query that would return:

1 - 1
1 - 0.6
2 - 1
2 - 1

Which is basically this: Return for each row then cod_data number, and the integer, if it is, or the part (like 0.6) if there is.
I know this can be done using math, but I canīt do it.

Thanks for any help.

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322

## Re: Super Query...

Originally posted by navarre_7
know this can be done using math, but I canīt do it.
Really?

What's the formula then....

3. Registered User
Join Date
Oct 2003
Posts
6

## Re: Super Query...

Thatīs the part I REALLY canīt do.
Do you think it can be done?

Originally posted by Brett Kaiser
Really?

What's the formula then....

4. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Well,

To get the first part about separating Interger values from decimal values you can do:

Code:
```DECLARE @x decimal(5,2)
SELECT @x = 1.06
SELECT FLOOR(@x), @x-FLOOR(@x)```
But how did you get the 1's from the 2? And why?

Can you give us a little background on what it is you're trying to do?

5. Registered User
Join Date
Oct 2003
Posts
6
Originally posted by Brett Kaiser
Well,

To get the first part about separating Interger values from decimal values you can do:

Code:
```DECLARE @x decimal(5,2)
SELECT @x = 1.06
SELECT FLOOR(@x), @x-FLOOR(@x)```
But how did you get the 1's from the 2? And why?

Can you give us a little background on what it is you're trying to do?
Iīll try...

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.

6. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Sounds like you need a rules table to store all the combinations.

I would then join to that and apply the rule that belongs to the set.

If you post DDL, sample data, and what the actual rules look like, we should be able to help...

Otherwise it's kind of tough..

7. Registered User
Join Date
Sep 2003
Location
New York, NY
Posts
136
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.

8. Registered User
Join Date
Aug 2003
Posts
3
or what u can do is if the floor returns 0 then u select @x. If the floor >0
then select @x-floor(@x)

9. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Perhaps if you gave us more than two sample records (and output) we could better figure out what you are trying to do.

blindman

10. Registered User
Join Date
Oct 2003
Posts
6
Originally posted by blindman
Perhaps if you gave us more than two sample records (and output) we could better figure out what you are trying to do.

blindman
OK. Iīll try.

Hereīs tsvd_vol_dados, which has the data that one user purchased:

1 1 1
2 1 1
3 2 1.5
4 5 2

Hereīs tsvd_parametros, which describes each type and itīs order in the list:

1 1 1
1 2 2
1 5 5
1 3 6
1 4 7

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:

SELECT DATA
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•