How to generate a score table easily instead of using too many case statements in SQL?

Question

question

Sunshine15 asked Jan 14, '21

How to generate a score table easily instead of using too many case statements in SQL?

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!

sql server 2017

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-01-18T09:59:51Z

Kev Riley answered Jan 18, '21

With a tally table (or dynamically generated one like the cte in my example) this is fairly trivial:

declare @StartValue decimal(10,6) =  -500.000000;
declare @ValueInterval decimal(10,6) = 5;
declare @ScoreStart decimal(10,1) = -50;
declare @ScoreInterval decimal(10,1) = 0.5;

WITH  Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ),                
      Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ),    
      Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),    
      Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),    
      Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),    
      Nbrs  ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )    

select 
    n,
    @StartValue + (@ValueInterval*(n-1)) - (@ValueInterval - 0.000001) as StartValue,
    @StartValue + (@ValueInterval*(n-1)) as EndValue,
    @ScoreStart + (@ScoreInterval*(n-1)) as Score
from ( SELECT ROW_NUMBER() OVER (ORDER BY n)  FROM Nbrs ) D ( n )    
where n <= 201;

You can read more about tally tables in Jeff Moden's excellent SSC article: https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question