Generating Sequnce number with float seed and float starting and end numbers

# Generating Sequnce number with float seed and float starting and end numbers

• Friday, February 01, 2013 2:35 PM

```How to generate the data as shown below, if given the starting value as 20.01 and the seed value as .01 upto 40.00Data
20.01
20.02
20.03
20.04
20.05
20.06
20.07
20.08
20.09
20.1
20.11
20.12
20.13
20.14
20.15```

Thanks,

Shalem

### All Replies

• Friday, February 01, 2013 2:43 PM

Pretty simple loop

```DECLARE @startNum decimal(8,2)
DECLARE @endNum decimal(8,2)
SET @startNum = 20.01
Set @endNum = 40.00
DECLARE @tmptbl TABLE ( counter decimal(8,2) )
WHILE (@startNum <= @endNum)
BEGIN
INSERT INTO @tmptbl SELECT @startNum
SELECT @startNum = @startNum + .01
END
select * from @tmptbl```

Chuck Pedretti | Magenic – North Region | magenic.com

• Friday, February 01, 2013 2:46 PM

But i dont wanna use the loops, since the range is very high it may cross 1 Lakh. So, expecting

something to use IDENTITY property or else CTE.

• Friday, February 01, 2013 2:53 PM

As we say up here: As you call in the wood, you will be answered.

If there is a context for your problem, please give that context. If you only say I want numbers in a sequence from 20 to 40 with a step of 0.01, you will get that answer.

So, do you have existing data in table you want to number? Do you want to import data and number it while you are it? Something else?

And which version of SQL Server do you have?

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

• Friday, February 01, 2013 3:22 PM

why not use an indentity (1,1) in your table

and then calculate the value with

```select (myid % 4000) /100.0
from mytable```

﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿
• Friday, February 01, 2013 3:51 PM

```with t as
(	select CONVERT(decimal(4,2),20.00) x
union all
select CONVERT(decimal(4,2),x + 0.01)
from t
where x < 40.00
)
select x
from t
OPTION (MAXRECURSION 2000)
```

Please Mark as Reply and Vote as Helpful if I helped.

Also please visit my blog http://msguy.net/

• Saturday, February 02, 2013 7:44 AM

Erland, thanks for your cooperation.

The scenario is like, we have starting float type value and ending float type value, between these two values we need to generate a values by adding float type seed value to the starting value.

The range may cross lakhs, so need optimistic solution without using loops and currsors and all.

We are using SQL 2005 as DB Server.

Shalem

• Saturday, February 02, 2013 10:30 AM

I will still have to make a guess what you are asking for. It sounds like you have an existing table, so I take my chances with an UPDATE:

```;WITH numbered AS
SELECT seqcol,
rowno = row_number() OVER (ORDER BY somecol),
cnt = COUNT(*) OVER()
FROM   tbl
)
UPDATE numbered
SET    seqcol = (number - 1) / (cnt - 1) * (@end - @start) + @start```
This is not tested, and might require some fine-tuning, but I hope that you are able to apply the suggestion on your actual problem.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

• Monday, February 04, 2013 8:10 PM

You've got some pretty good answers.

just wanted to add that data type float cannot represent every instance of 0.01 exactly. This makes it a poor choice if you need exact values.

You will not have this problem with data type decimal or any of the integer data types (such as int and bigint).

Gert-Jan