Inserting an incremental range of decimal numbers into a table

  • paul 69259

    SSCrazy

    Points: 2890

    Hi can anyone help me with some SQL code to insert some decimal numbers into a table please?

    I have a table with an ID field, a lower field, an upper field and a flat rate.

    What I'm trying to do is a script to insert into the lower field values 0.00, 1.01, 2.01,3.01,4.01,5.01......up to 499.01 and in the upper field the values 1.00,2.00,3.00,4.00.....up to value 500.00

    Does anyone have any idea please? I've got some sort of idea how I would go about doing this please?

    I've tried to make a start   it's not quite there and the master..[spt_values] doesn't give decimal numbers and I'm not sure how it works.

    Thanks in advance

    insert into chargebreaks (chargeid,lower,upper,flatrate)
    values
    2218,not sure, select DISTINCT n = NUMBER
    FROM master..[spt_values]
    WHERE number BETWEEN 1 AND 500,0.28

     

  • Neil Burton

    SSC-Insane

    Points: 22205

    WITH Numbers  AS
    (
    SELECT CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS decimal(8,2)) AS N
    FROM
    (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
    CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t1(N)
    CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t2(N)
    )
    SELECT
    N-0.99 AS Lower
    ,N AS Upper

    FROM

    Numbers
    WHERE N<= 500

    Does that work?


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • paul 69259

    SSCrazy

    Points: 2890

    Thanks Neil, unfortunately not. It just gives back the numbers 499.01 for lower and 500.00 for upper but I need the numbers from 1.00 onwards

     

    Thank you

    Paul

  • Neil Burton

    SSC-Insane

    Points: 22205

    paul 69259 wrote:

    Thanks Neil, unfortunately not. It just gives back the numbers 499.01 for lower and 500.00 for upper but I need the numbers from 1.00 onwards

    Thank you

    Paul

    I'm not sure what you mean there?  Are you saying it only returns one row?  I think you're asking for 500 rows with n.01 as the lower number (0.01,1.01 etc.) and n+1.00 as the upper (1.00,2.00 etc) , is that right?


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • paul 69259

    SSCrazy

    Points: 2890

    Yes,that's right Neil. It's only returning 1 row but I need 500 rows as you have shown

     

    Thank you

    Paul.

  • Neil Burton

    SSC-Insane

    Points: 22205

    Have you copied all of the query? It should be returning 500 rows.  The last line is 'WHERE N<= 500'.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • paul 69259

    SSCrazy

    Points: 2890

    Ahh my mistake. On the email the WHERE N<= 500 part came out as 'WHERE N&lt;= 500' and was giving me an error so I altered it to just 'WHERE N= 500'

     

    That's what I needed. Thank you Neil

  • Neil Burton

    SSC-Insane

    Points: 22205

    You're welcome Paul.

    That query will produce up to 1000 rows if needs be just by changing the '<=500' to what you want.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • paul 69259

    SSCrazy

    Points: 2890

    Do you know the syntax for me adding it to the insert into statement please Neil?

    I'm trying like this, the chargeid would be 2218, the upper and lower are obtained from your code and the flat rate would be 0.28

    insert into chargebreaks (chargeid,upper,lower,flatrate)
    select 2218,(

    WITH Numbers AS
    (
    SELECT CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS decimal(8,2)) AS N
    FROM
    (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
    CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t1(N)
    CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t2(N)
    )
    SELECT
    N-0.99 AS Lower
    ,N AS Upper

    FROM

    Numbers
    WHERE N<= 500.),0.28

    thank you

    Paul

     

  • Neil Burton

    SSC-Insane

    Points: 22205

    WITH Numbers AS
    (

    SELECT CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS decimal(8,2)) AS N
    FROM
    (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N) --10 Rows
    CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t1(N) --100 Rows
    CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t2(N) --1000 Rows
    /**********Add these lines in if you ever need more rows *************/
    --CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t3(N)--10000 Rows
    --CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t4(N)--100000 Rows
    --CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t5(N)--10000000 Rows

    )

    INSERT INTO chargebreaks
    (
    ChargeID
    ,Upper
    ,Lower
    ,FlatRate
    )

    SELECT
    2218
    ,N-0.99 AS Lower
    ,N AS Upper
    ,0.28
    FROM
    NUmbers
    WHERE N<= 500

    Here you go.

    Do you know what the query I provided is actually doing?


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • paul 69259

    SSCrazy

    Points: 2890

    Well I can see the first 3 rows are representing the tens, hundreds and thousands of the figure and I assume the cross join is what puts them side by side but I'm pretty lost by the rest of it to be honest.

  • Jeff Moden

    SSC Guru

    Points: 996645

    So, Paul... what are you going to do if a value comes in at, say, 2.005?  That won't be caught by any range you've defined.  Are you absolutely sure that your absolute scale limit will always be relegated to only 2 decimal places and will never be otherwise?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • paul 69259

    SSCrazy

    Points: 2890

    Hi Jeff,

    I'll give you a bit of background to it all.

    We have a customer that is charging 0.28p per kilo as a surcharge for international shipments.

    The issue is that our software will charge 0.42p for a 1.5 kilo shipment but the customer wants to charge 0.56p because it's over 1 kg.

    To get that set up correctly for the customer we have break points. If we set up the breakpoints so that the first breakpoint is from 0 to 1 at .28p and then from 1.01 to 2 at another .28p and from 2.01 to 3 at another .28p etc..... all the way up to 500 kilos.

    Rather than the customer entering a line for each break point in our software they asked if we could do it in the SQL database for them.

    We only go to 2 decimal places so if an item weighed 2.005 kilo then it would be charged 56p as it would be treated as 2 kilos.

     

  • Jeff Moden

    SSC Guru

    Points: 996645

    So why not make it truly easy on yourself and use a "Open/Closed" bit of code and setup.  For example, instead of say something like 2.01 to 3.oo as a range, use whole numbers like 2 to 3 where the code treats that as >2 and <= 3 for a range?

    Or, flip it around to be "more normal" where 2 to 3 means >= 2 and < 3 range?

    This can also work with fractions.  Fore example, you can have 1.0 to 1.5 and 1.5 to 2 meaning (in the latter example) >= 1.0 and < 1.5 and >= 1.5 and < 2 for ranges?  That way, if you ever suffer a scale change, it's already handled.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • paul 69259

    SSCrazy

    Points: 2890

    Unfortunately it's not as black and white as that Jeff.

    This is just one surcharge for one customer. We have many different customers that can have upwards of 30 different surcharges each for many different services and each service can have many different surcharges.

    Each customer and surcharge will have different break points. So, for example, one surcharge may charge £5 per kilo up to 20 kilos and £2.50 for every kilo after that. Others may not break until 500 kilos for pallets and may incur a surcharge for every 250 kilos after the first 500. There are an infinite number of different ways the customers break the surcharges down so this is why we have the breaks system, to enable our software to cope with all the different variations between surcharges and customers.

    This particular one is charged at .28p per kilo but the customer wants it to charge at 2*.28p for anything over 1 kilo and 3*.28p for anything over 2 kilos hence the breaks from o to 1, 1.01 to 2, 2.01 to 3 etc... up to 500 kilos.

     

    Thank you

    Paul.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply