Click here to Skip to main content
15,881,248 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am having hard time thinking for a posible solution for this problem: i have one table that has value as
C#
name    value
A        0
A        10
A        20
B        0
B        50
B        70
B        100

Requirement: i) find min and max value for each name i.e.
C#
name min max
A     0   20
B     0   100

then divide the min and max to 10 equal parts and display like:
C#
name range1 range2
A      0       2
A      2       4
A      4       6
A      6       8
A      8       10
A      10      12
A      12      14
A      14      16
A      16      18
A      18      20

and similarly for B.

I tried something like this:
C#
SELECT
name,
MIN,
MAX,
(MAX - MIN)/10 PARTITION_VALUE,
FROM(SELECT
name,
MIN(value) MIN,
MAX(value) MAX
FROM [table]
GROUP BY name) A

calculated min value, max value and the partition_value that will help in defining the range.. i.e. dividing min and max value in 10 different parts.
I am just unable to proceed further.

Thanks
Posted
Updated 30-Apr-15 19:55pm
v2

HI here is the sql query from which you can get max & min values of names


select name, (select MIN(value) from table1 b where b.name=a.name) as mini,(select MAx(value) from table1 c where c.name=a.name) as maxi from table1 a group by name



thanks.
 
Share this answer
 
hi Deepak,

Thank you for the answer, but my desired output is something else.
I need to first find min and max values as per name (which is the output from your query).
After that based on those value, I need to create 10 rows with equal partitions for each name.
its like A has min as 0 and max as 20.
so its partition would look like
A 0
A 2
A 4
A 6
A 8
A 10
A 12
A 14
A 16
A 18
A 20

After doing this, i require the output as:

A 0 2
A 2 4
A 4 6
A 6 8
A 8 10
A 10 12
A 12 14
A 14 16
A 16 18
A 18 20
 
Share this answer
 
SQL
WITH DATA_RANGE AS(
SELECT NAME, DATA_VALUE, LEAD(DATA_VALUE) OVER (PARTITION BY NAME ORDER BY DATA_VALUE) NEXT_VALUE FROM
(
SELECT NAME,MIN_VAL V1,MIN_VAL+PARTITION V2,MIN_VAL+PARTITION*2 V3,MIN_VAL+PARTITION*3 V4,MIN_VAL+PARTITION*4 V5,MIN_VAL+PARTITION*5 V6
,MIN_VAL+PARTITION*6 V7,MIN_VAL+PARTITION*7 V8,MIN_VAL+PARTITION*8 V9,MIN_VAL+PARTITION*9 V10,MAX_VAL V11
FROM(
SELECT NAME,MIN_VAL,MAX_VAL,(MAX_VAL-MIN_VAL)/10 PARTITION
FROM(
SELECT NAME,MAX(VALUE) MAX_VAL, MIN(VALUE) MIN_VAL FROM TAB
GROUP BY NAME) A) A) A
UNPIVOT
(DATA_VALUE
FOR DATA_TYPE IN (V1,V2,V3,V4,V5,V6,V7,V8,V9,V10,V11))
ORDER BY NAME, DATA_VALUE)
SELECT NAME, DATA_VALUE, NEXT_VALUE FROM DATA_RANGE
WHERE NEXT_VALUE IS NOT NULL
ORDER BY NAME,DATA_VALUE
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900