2

I have a table in a SQL Server 2008 database with a number column that I want to arrange on a scale 1 to 10.

Here is an example where the column (Scale) is what I want to accomplish with SQL

Name    Count   (Scale)
----------------------
A       19      2       
B       1       1
C       25      3
D       100     10
E       29      3
F       60      7

In my example above the min and max count is 1 and 100 (this could be different from day to day).

I want to get a number to which each record belongs to.

1 = 0-9
2 = 10-19
3 = 20-29 and so on...

It has to be dynamic because this data changes everyday so I can not use a WHERE clause with static numbers like this: WHEN Count Between 0 and 10...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SunnyJones
  • 23
  • 3

4 Answers4

1
WITH MinMax(Min, Max) AS (SELECT MIN(Count), MAX(Count) FROM Table1)
SELECT Name, Count, 1 + 9 * (Count - Min) / (Max - Min) AS Scale
FROM Table1, MinMax
CrimsonKing
  • 2,696
  • 1
  • 14
  • 11
1

You can make Scale column a PERSISTED COMPUTED column as:

alter table test drop column Scale

ALTER TABLE test ADD
Scale AS (case when Count between 0 and 9 then 1 
               when Count between 10 and 19 then 2
               when Count between 20 and 29 then 3
               when Count between 30 and 39 then 4
               when Count between 40 and 49 then 5 
               when Count between 50 and 59 then 6
               when Count between 60 and 69 then 7
               when Count between 70 and 79 then 8 
               when Count between 80 and 89 then 9
               when Count between 90 and 100 then 10
          end
          )PERSISTED
GO

DEMO

Deepshikha
  • 9,896
  • 2
  • 21
  • 21
1

Try this, though note technically the value 100 doesn't fall in the range 90-99 and therefore should probably be classed as 11, hence why the value 60 comes out with a scale of 6 rather than your 7:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

create table #scale
(
    Name Varchar(10),
    [Count] INT
)

INSERT INTO #scale
VALUES
    ('A', 19),
    ('B', 1),
    ('C', 25),
    ('D', 100),
    ('E', 29),
    ('F', 60)


SELECT name, [COUNT],  
    CEILING([COUNT] * 10.0 / (SELECT MAX([Count])  - MIN([Count]) + 1 FROM #Scale)) AS [Scale]
FROM #scale

Results:

| NAME | COUNT | SCALE |
|------|-------|-------|
|    A |    19 |     2 |
|    B |     1 |     1 |
|    C |    25 |     3 |
|    D |   100 |    10 |
|    E |    29 |     3 |
|    F |    60 |     6 |

This gets you your answer where 60 becomes 7, hence 100 is 11:

SELECT name, [COUNT],  
    CEILING([COUNT] * 10.0 / (SELECT MAX([Count])  - MIN([Count]) FROM #Scale)) AS [Scale]
FROM #scale
Steve Ford
  • 7,433
  • 19
  • 40
  • Thank you Steve! This helped med a lot. your right about the 100 that should be 99 in my example. I will never be as good as the database itself ;) – SunnyJones Nov 27 '14 at 16:20
1
select ntile(10) over (order by [count])
Dvintila
  • 212
  • 3
  • 13