Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Generating Sequnce number with float seed and float starting and end numbers

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

  • Friday, February 01, 2013 2:35 PM
     
      Has Code
    Sign In to Vote
    0
     
    How to generate the data as shown below, if given the 
    starting value as 20.01 and the seed value as .01 upto 40.00
    Data
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
     
      Has Code
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    Chuck, thanks for your answer.

    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.

    Thanks in advance.

     
  • Friday, February 01, 2013 2:53 PM
     
     
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    0
     

    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
     
     
    Sign In to Vote
    0

    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.

    Thanks in advvance.

    Shalem

     
  • Saturday, February 02, 2013 10:30 AM
     
      Has Code
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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