Solved

# Simulate a Lookup function in SQL

Posted on 2014-09-03
165 Views
I have a table on sheet1 that has over 300K rows of dollar values  in MS Excel.  On Sheet2 I have a have a second table  I s use for a lookup  and that contains an amount and a revenue band:
amt      BAND
0      UNDER 1K
1000      \$1k>5K
5000      \$5K > \$10K
10000      \$10 > \$25K
25000      \$25K > \$50K
50000      \$50K > \$100K
100000      \$100K > \$500K
500000      \$500 > \$1MILL
1000000      \$1MILL+

I use this Lookup table  to place a revenue band value in the  first table: =Lookup (A2, Sheet2!A\$2:A\$10, B\$2, B\$10)

--so that the result in sheet one would look something like:
A1               B1
AMT            BAND
1000         \$1k>5K
27012       \$25K > \$50K

This works fine in Excel , except that it tales al ong time to render at at times crashes. Is ther a way to simulate this in SQL, with two tables-- a values table and a lookup table?

Thanks!!
0
Question by:wasmithpfs

LVL 39

Expert Comment

I would do this in a case statement:

``````select
case
when amount < 1000       then cast( 'UNDER 1K' as varchar(100))
when amount < 5000      then cast( ' \$1k>5K' as varchar(100))
when amount < 10000     then cast(  '\$5K > \$10K' as varchar(100))
...
else cast(  '  \$1MILL+' as varchar(100))
end    as  Band

``````
0

LVL 21

Expert Comment

In SQL you would just need a single table, but any reason you just don't make a VBA function to do this?  Or does your lookup sheet change often?
0

LVL 44

Accepted Solution

The preferred approach in SQL will be to have a table with both lower and upper bound values for the ranges as well as the BAND label text (see below)
``````LowAmt	HiAmt	BAND
0	999	UNDER 1K
1000	4999	\$1k>5K
5000	9999	\$5K > \$10K
10000	24999	\$10 > \$25K
25000	49999	\$25K > \$50K
50000	99999	\$50K > \$100K
100000	499999	\$100K > \$500K
500000	999999	\$500 > \$1MILL
1000000	 999999999999999 	\$1MILL+
``````
You can join another table to this one using a Between clause.
Example:
``````Select MyTable.*, BandLookup.BAND
From MyTable, BandLookup
Where MyTable.Amt Between BandLookup.LowAmt and BandLookup.HiAmt
``````
It is also possible to have an open-ended range, but your SQL will need to accommodate it, perhaps with a UNION query or IIF()/CASE

Are you using Access or SQL Server (or similar) database?
0

LVL 44

Expert Comment

I've also used an ADODB recordset object to do a join of Excel worksheet data and populate a range using the CopyFromRecordset method.  All of this was in the Excel VBA run-time environment.
0

Author Closing Comment

Perfect!
0

## Featured Post

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…