?
Solved

approximate vlookup in sql

Posted on 2011-10-20
7
Medium Priority
?
447 Views
Last Modified: 2012-05-12
How does one do a function in sql like approximate vookup(end is true and not false). do you still use some type of join for this?

sql '05
0
Comment
Question by:k1ng87
7 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 36999718
can you explain what you want more?
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36999720
yes we will have to use join
0
 
LVL 1

Author Comment

by:k1ng87
ID: 37000092
Range | Value
------------------
1-4           a
5-8           b
11-15       c

If I had another table with a value 3 and I joined it on column Range, I should get back value A from the value column. If i was joining on a value that was 9, I should get back since 9 falls out in any ranges.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:k1ng87
ID: 37000096
i should get back a null for a value of 9
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37002872
Yes, it's a join, but it needs to be LEFT join, since you may not find a match:

SELECT ..., vt.value, ...
FROM main_table mt
...other join(s)...
LEFT OUTER JOIN vlookup_table vt ON
    mt.search_value BETWEEN vt.range_start AND vt.range_end
0
 
LVL 1

Author Comment

by:k1ng87
ID: 37031504
you have a range_start and range_end..i'm assuming i'd have to breakout the range into columns?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37032014
That would be ideal.

But if you're always using the "-" to indicate the range, you could do this:

LEFT OUTER JOIN vlookup_table vt ON
    mt.search_value BETWEEN LEFT(vt.range, CHARINDEX('-', vt.range) - 1) AND SUBSTRING(vt.range, CHARINDEX('-', vt.range) + 1, 100)

To simplify coding, if you have only a single value in the range, still specify it as a range, like so:

1-4,
5-5,
6-9,
...
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 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