countrymeister
asked on
SQL Syntax
I have two tables
Table1
ReturnType varchar(10)
ReturnValue decimal
Table stores values such as (Daily, Weekly, Monthly) in the Return type column
and various return values
Table2
RangeType varchar(10)
RangeValue 0.1, 0.2. 0.5
Table 2 has RangeType which also stores values such as (Daily, Weekly, Monthly
example
Daily 0.5 ( which means greater than 0.5
Daily 0.10 ( greater than 0.10)
Daily 0.20 ( greater than 0.2)
I need to join these two tables and generate a new column where based on the value in ReturnValue I assign it to a buket that falls in the RangeValue
Example
ReturnType, ReturnValue, Range
Daily 0.21 > than 0.20
Daily 0.60 > than 0.50
Table1
ReturnType varchar(10)
ReturnValue decimal
Table stores values such as (Daily, Weekly, Monthly) in the Return type column
and various return values
Table2
RangeType varchar(10)
RangeValue 0.1, 0.2. 0.5
Table 2 has RangeType which also stores values such as (Daily, Weekly, Monthly
example
Daily 0.5 ( which means greater than 0.5
Daily 0.10 ( greater than 0.10)
Daily 0.20 ( greater than 0.2)
I need to join these two tables and generate a new column where based on the value in ReturnValue I assign it to a buket that falls in the RangeValue
Example
ReturnType, ReturnValue, Range
Daily 0.21 > than 0.20
Daily 0.60 > than 0.50
ASKER
This does not work
Sorry, made a mistake. This is what happens when you post an untested solution.
Here, this should work:
Here, this should work:
Select t1.ReturnType, t1.ReturnValue,
MAX(t2.RangeValue) AS Range
FROM Table1 t1 LEFT JOIN Table2 t2
ON t1.ReturnType = t2.RangeType and t1.ReturnValue > t2.RangeValue
GROUP BY t1.ReturnType, t1.ReturnValue
ASKER
chauu
I need the min value of ReturnValue greater than the RangeValue
Example Table 2 has the following values for Daily
0.1
0.2
0.5
Tthen if table 1 has Daily , and the Range Value = 0.15
This should give me 0.2 from Table 2
I need the min value of ReturnValue greater than the RangeValue
Example Table 2 has the following values for Daily
0.1
0.2
0.5
Tthen if table 1 has Daily , and the Range Value = 0.15
This should give me 0.2 from Table 2
How do want equal values to be handled? For example, if table1 has Daily and a Range Value of .10, does it belong with .1 or .2 of table2?
Some more sample data for the two tables (e.g. include weekly and monthly and their respective ranges) plus your expected results would help a lot.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
by: awking00
here is some data, for every row in Table1 I need to assign a single range value
The final resulkt set should have
ID ReturnType ReturnValue , RangeValue
Table 1
ID ReturnType ReturnValue
226568 Daily -0.02813024
226570 Daily 0.00406007
226572 Daily -0.01723199
226573 Daily 0.02970666
226575 Daily -0.03828653
226577 Daily 0.01418188
226579 Daily -0.06490917
226580 Daily 0.00652104
226582 Daily 0.00661904
226584 Daily 0.11295950
Table2
ID RangeType RangeValue
1 Daily -0.50000000
2 Daily -0.25000000
3 Daily -0.10000000
4 Daily -0.05000000
5 Daily 0.00000000
6 Daily 0.05000000
7 Daily 0.10000000
8 Daily 0.25000000
9 Daily 0.50000000
here is some data, for every row in Table1 I need to assign a single range value
The final resulkt set should have
ID ReturnType ReturnValue , RangeValue
Table 1
ID ReturnType ReturnValue
226568 Daily -0.02813024
226570 Daily 0.00406007
226572 Daily -0.01723199
226573 Daily 0.02970666
226575 Daily -0.03828653
226577 Daily 0.01418188
226579 Daily -0.06490917
226580 Daily 0.00652104
226582 Daily 0.00661904
226584 Daily 0.11295950
Table2
ID RangeType RangeValue
1 Daily -0.50000000
2 Daily -0.25000000
3 Daily -0.10000000
4 Daily -0.05000000
5 Daily 0.00000000
6 Daily 0.05000000
7 Daily 0.10000000
8 Daily 0.25000000
9 Daily 0.50000000
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Should have added the types to the join to separate daily from weekly from monthly.
...
where t1.returnvalue between z.rangevalue and z.nextvalue
and t1.returntype = z.rangetype
...
...
where t1.returnvalue between z.rangevalue and z.nextvalue
and t1.returntype = z.rangetype
...
Open in new window