How to generate a Score table easily?

Rainbow1517 21 Reputation points
2021-01-14T01:00:33.06+00:00

I would like to ask how to generate a Score table easily. The Score table has fields ID, StartValue, EndValue and Score, see below.
The logic is if a field is between the StartValue and EndValue, a score is given.

The StartValue and the EndValue have six decimal places.
An interval between two adjacent numbers for the Score field is 0.5.
An interval between the StartValue and the EndValue is around 5.
The values of the EndValue field are from -500 to 500.

Score Table
ID StartValue EndValue Score
1 0.000001 5.000000 0.5
2 5.000001 10.000000 1
3 10.000001 15.000000 1.5
4 15.000001 20.000000 2
.....
100 455.000001 500.000000 50

101 -4.999999 0.000000 0
102 -9.999999 -5.000000 -0.5
103 -14.99999 -10.000000 -1
104 -19.99999 -15.000000 -1.5
105 -24.99999 -20.000000 -2
......
xxx -504.999999 -500.000000 -50

Thanks in advance!

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-01-14T01:38:44.83+00:00

    Hi @Rainbow1517 ,

    Welcome to Microsoft Q&A!

    Please refer below:

    create table Score   
    (ID int,   
    StartValue decimal(12,6),  
     EndValue  decimal(12,6),  
     Score decimal(5,1))  
     
     ;with cte as (  
     select row_number() over (order by (select null))  as n  
           from   master..spt_values    
              )  
     insert into Score  
     select n,0.000001+5*(n-1),5.000000*n,0.5*n from cte where n<=100  
     union all  
     select n,0.000001-5*(n-100),-5.000000*(n-101),-0.5*(n-101) from cte   
     where n>100 and n<=201  
      
    select * from Score  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful