Cross 2 tables to get a result between values....

[andrecabrita]

Hi,

I have two table, table1 where I have the fish weight and the FCR and table 2 the average weight on each pond, so I would like to have a select that allows me to cross the two table and get the FCR correpond, like shown here:

Thank you very much

Result
pond FCR
1 0,25
2 0,24
3 0,1
4 0,2

Table1
grams FCR
0 0,25
100 0,24
200 0,23
300 0,22
400 0,2
500 0,1

Table2
pond g
1 22
2 124
3 1500
4 450

 

[simian336]

Try this....

create table #table1 (
grams int,
fcr char(10))

insert into #table1 values (0,'0,25')
insert into #table1 values (100,'0,24')
insert into #table1 values (200,'0,23')
insert into #table1 values (300,'0,22')
insert into #table1 values (400,'0,2')
insert into #table1 values (500,'0,1')

select * from #table1

create table #table2 (
pond int,
g int)

insert into #table2 values(1,22)
insert into #table2 values(2,124)
insert into #table2 values(3,1500)
insert into #table2 values(4,450)

select * from #table2


with test as
(
select pond, g,
case
when g between 0 and 99 then 0
when g between 100 and 199 then 100
when g between 200 and 299 then 200
when g between 300 and 399 then 300
when g between 400 and 499 then 400
else 500
end as low
from #table2
)
Select test.pond, t1.FCR
from test
join #table1 t1
on test.low=t1.grams


Simi

 

[simian336]

Forgot the order by...

with test as
(
select pond, g,
case
when g between 0 and 99 then 0
when g between 100 and 199 then 100
when g between 200 and 299 then 200
when g between 300 and 399 then 300
when g between 400 and 499 then 400
else 500
end as low
from #table2
)
Select test.pond, t1.FCR, test.low
from test
join #table1 t1
on test.low=t1.grams
order by test.pond

Simi