--I have two tables with no common columns:
declare @Graph_Range table (Start_Range decimal(3,2), End_Range decimal(3,2))
insert @Graph_Range
select 0.10, 0.20 union all
select 0.20, 0.30 union all
select 0.30, 0.40
declare @Graph_data table (Asset int, factor decimal(3,2), [case] int)
insert @Graph_data
select 500, 0.12, 1 union all
select 270, 0.13, 2 union all
select 300, 0.27, 3 union all
select 240, 0.23, 4 union all
select 200, 0.13, 5 union all
select 100, 0.12, 6
--I need results as Lower Limit Upper Limit Sum_Asset
--0.10 0.20 1,070
--0.20 0.30 540
--0.30 0.40 -
--i.e. it should give sum of asset values in case the relevant factor falls between start and end range.
select
Start_Range,
End_Range,
SUM(Asset) as Sum_Asset
from
(
SELECT
A.Start_Range,
A.End_Range,
B.Factor,
B.Asset
FROM @Graph_Range AS A
LEFT JOIN @Graph_Data AS B ON B.Factor between A.Start_Range and A.End_Range
)dt
GROUP BY Start_Range, End_Range
Too old to Rock'n'Roll too young to die.