Results 1 to 10 of 10

Thread: Super Query...

  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Cool Unanswered: Super Query...

    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. #2
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    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. #4
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    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. #6
    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..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    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. #8
    Join Date
    Aug 2003
    Posts
    3

    Post

    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. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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. #10
    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:

    COD_VOLUME COD_DADO QTD_DADOS
    1 1 1
    2 1 1
    3 2 1.5
    4 5 2

    cod_dado is the type, qtd_dados are the amount of that type.

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

    COD_GRUPO NUM_ORDEM COD_DADO
    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
  •