February 20, 2012 at 8:01 am
I inherited a project originally developed within Access. One key component of this project is a huge, messy function written in VBA (12 printed pages of nested IF/THEN statements) to select a state code based on a confusing combination of region, division and group codes.
Here's the basic idea: If all you have is a region code, then it gets a default status code. If you have a region or code group, then it overrides the default status code for that region. There are a ton of division and region codes, many of which are not listed in the function, so if it is not found then the default status code is used (based on region).
I'm trying to rebuild this entire project in TSQL and use a lookup table rather than a UDF. So far, here's what a slice of what I've got:
drop table #xref
drop table #test
CREATE TABLE #xref (
id INT IDENTITY(1,1) PRIMARY KEY,
region TINYINT,
division VARCHAR(3),
[group] VARCHAR(6),
[status] VARCHAR(16)
)
INSERT INTO #xref SELECT 6, NULL, NULL, 'XA'
INSERT INTO #xref SELECT 6, '150', NULL, 'AI'
INSERT INTO #xref SELECT 6, '151', NULL, 'AI'
INSERT INTO #xref SELECT 6, '152', NULL, 'AI'
INSERT INTO #xref SELECT 6, '152', 'A1', 'AJ'
INSERT INTO #xref SELECT 6, '152', 'A2', 'AJ'
INSERT INTO #xref SELECT 6, '152', 'A3', 'AK'
INSERT INTO #xref SELECT 6, '155', 'A1', 'AJ'
INSERT INTO #xref SELECT 7, NULL, NULL, 'YA'
INSERT INTO #xref SELECT 7, '201', NULL, 'CA'
INSERT INTO #xref SELECT 7, '201', 'E', 'CB'
-- about another 1500 entries in the #xref table
CREATE TABLE #test (
id INT IDENTITY(1,1) PRIMARY KEY,
region TINYINT,
division VARCHAR(3),
[group] VARCHAR(6)
)
INSERT INTO #test SELECT 6, '150', ''-- AI
INSERT INTO #test SELECT 6, '152', 'A2'-- AJ
INSERT INTO #test SELECT 6, '152', 'A4'-- AI
INSERT INTO #test SELECT 7, '299', ''-- YA
INSERT INTO #test SELECT 7, '201', 'B'-- CA
INSERT INTO #test SELECT 7, '201', 'E'-- CB
INSERT INTO #test SELECT 0, '301', 'P9'-- ??
SELECT
ISNULL(X.[status], '') AS x,
ISNULL(Y.[status], '') AS y,
ISNULL(Z.[status], '') AS z,
COALESCE(X.[status], Y.[status], Z.[status], '??') AS status_code,
E.*
FROM #test AS E
LEFT JOIN #xref AS X
ON E.region = X.region
AND E.division = X.division
AND E.[group] = X.[group]
LEFT JOIN #xref AS Y
ON E.region = Y.region
AND E.division = Y.division
AND Y.[group] IS NULL
LEFT JOIN #xref AS Z
ON E.region = Z.region
AND Z.division IS NULL
AND Z.[group] IS NULL
I need to join this to a table (in place of #test) with 200,000,000+ rows. While my code works, I'm thinking there has to be a better way.
Comments and suggestions are appreciated.
February 20, 2012 at 8:42 am
It should not be an issue if you have the proper indexes. I would think that there should be one on Region, Division, Group. If your data is in that table, you can't really do much more than build the proper indexes and/or filter it down first.
Jared
CE - Microsoft
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply