Extensive Use pf Case Statement

Hi,

I want to generate a table output as per table a based on table b. In this case Range field os populated based on Shape and Process field values. You can see in both processes range is different. Now this is varies from shapes & processes which is stored in seperate master table which contains range ex. For Process_1 it is 0.5 & for Process_2 it is 0.10.

I have tried used case statement but I am unable to generate this dynamically in a single query. Can anyone Help me How can I achive this task?

Table A

Barcode

Shape

Process

Weight

Range

1

Shap_1

Proc_1

0.12

0.11-0.15

2

Shap_1

Proc_1

0.16

0.16-0.20

3

Shap_1

Proc_1

0.06

0.06-0.10

4

Shap_1

Proc_1

0.21

0.21-0.25

5

Shap_1

Proc_1

0.13

0.11-0.15

6

Shap_1

Proc_2

0.18

0.11-0.20

7

Shap_1

Proc_2

0.13

0.11-0.20

8

Shap_1

Proce_2

0.24

0.21-0.30

9

Shap_1

Proce_2

0.07

0.00-0.10

10

Shap_1

Proce_2

0.33

0.31-0.40

Table B

Barcode

Shape

Process

Weight

1

Shap_1

Proc_1

0.12

2

Shap_1

Proc_1

0.16

3

Shap_1

Proc_1

0.06

4

Shap_1

Proc_1

0.21

5

Shap_1

Proc_1

0.13

6

Shap_1

Proc_2

0.18

7

Shap_1

Proc_2

0.13

8

Shap_1

Proce_2

0.24

9

Shap_1

Proce_2

0.07

10

Shap_1

Proce_2

0.33

Nilkanth Desai

create table TableB(Barcode int, Shape char(6), Process varchar(8), Weight decimal(5,2))
insert into TableB(Barcode ,Shape , Process ,Weight)
select 1 , 'Shap_1', 'Proc_1' , 0.12 union all
select 2 , 'Shap_1', 'Proc_1' , 0.16 union all
select 3 , 'Shap_1', 'Proc_1' , 0.06 union all
select 4 , 'Shap_1', 'Proc_1' , 0.21 union all
select 5 , 'Shap_1', 'Proc_1' , 0.13 union all
select 6 , 'Shap_1', 'Proc_2' , 0.18 union all
select 7 , 'Shap_1', 'Proc_2' , 0.13 union all
select 8 , 'Shap_1', 'Proc_2', 0.24 union all
select 9 , 'Shap_1', 'Proc_2', 0.07 union all
select 10 , 'Shap_1', 'Proc_2', 0.33

create table Master(Process varchar(8), Range decimal(5,2))
insert into Master(Process, Range) values('Proc_1',0.05)
insert into Master(Process, Range) values('Proc_2',0.10)

select t.Barcode,
t.Shape,
t.Process,
t.Weight,
floor(t.Weight/m.Range)*m.Range+0.01 as RangeFrom,
floor(t.Weight/m.Range)*m.Range+m.Range as RangeTo
from TableB t
inner join Master m on m.Process=t.Process
order by t.Barcode