Inserting an incremental range of decimal numbers into a table
-
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 -
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
,NAS Upper
FROM
Numbers
WHERE N<= 500Does 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 PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
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
- 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 PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Yes,that's right Neil. It's only returning 1 row but I need 500 rows as you have shown
Thank you
Paul.
-
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 PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Ahh my mistake. On the email the WHERE N<= 500 part came out as 'WHERE N<= 500' and was giving me an error so I altered it to just 'WHERE N= 500'
That's what I needed. Thank you Neil
-
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 PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
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
,NAS Upper
FROM
Numbers
WHERE N<= 500.),0.28thank you
Paul
-
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
,NAS Upper
,0.28
FROM
NUmbers
WHERE N<= 500Here 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 PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
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.
-
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
-
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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 15 total)
You must be logged in to reply to this topic. Login to reply