Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Syntax

Posted on 2013-11-14
10
Medium Priority
?
221 Views
Last Modified: 2013-11-20
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
0
Comment
Question by:countrymeister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39649462
I think this should work. I have not tested it.
Select t1.ReturnType, t1.ReturnValue,  
MIN(t2.RangeValue) OVER (PARTITION BY t2.RangeType ORDER BY t2.RangeValue) AS Range
FROM Table1 t1 INNER JOIN Table2 t2 ON t1.ReturnType = t2.RangeType 

Open in new window

0
 
LVL 1

Author Comment

by:countrymeister
ID: 39649581
This does not work
0
 
LVL 25

Expert Comment

by:chaau
ID: 39649611
Sorry, made a mistake. This is what happens when you post an untested solution.
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

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:countrymeister
ID: 39651117
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
0
 
LVL 32

Expert Comment

by:awking00
ID: 39651242
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?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39651250
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.
0
 
LVL 25

Accepted Solution

by:
chaau earned 1000 total points
ID: 39651276
You are right. Please use this:
Select t1.ReturnType, t1.ReturnValue,  
MIN(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 

Open in new window

0
 
LVL 1

Author Comment

by:countrymeister
ID: 39651365
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
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1000 total points
ID: 39651442
select t1.id, t1.returntype, t1.returnvalue, z.rangevalue
from table1 as t1,
(select x.rangetype, x.rangevalue, y.rangevalue as nextvalue from
 (select rangetype, rangevalue,
  row_number() over (order by rangetype, rangevalue) rn
  from table2) as x,
 (select rangetype, rangevalue,
  row_number() over (order by rangetype, rangevalue) rn
  from table2) as y
 where x.rn = y.rn - 1) as z
where t1.returnvalue between z.rangevalue and z.nextvalue
order by t1.id;
0
 
LVL 32

Expert Comment

by:awking00
ID: 39651455
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
...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question