March 21, 2016 at 9:03 am
Hi folks
Hoping someone can help me find a more efficient way of doing the following.
I have a table of values that looks a little like this..
TableA
UniqueId, Type, Value
1,A,15.12
2,A,44.11
3,A,56.23
4,B,12
5,B,25.7
6,B,45.32
And another table which is used as a lookup
TableB
UniqueId, Type, Value, Band
1,A,20,Band1
2,A,30,Band2
3,A,40,Band3
4,A,60,Band4
5,B,15,Band1
6,B,40,Band2
7,B,60,Band3
I want to join by Type from TableA to TableB, then find the Band from TableB that Value in TableA fits within.
e.g. the second row in TableA (Type A, value=44.11) would equal BAND3 (44.11 greater than Band3 value, but less than Band4 value).
Any of you clever folks have a neat approach to this? The closest (roughly) analogous example would be an Excel Vlookup with a TRUE parameter.
Les
March 21, 2016 at 10:00 am
Hi,
this is close, but not quite right, I think... First off, since you're new, it would help those folks trying to help you if you could post your table structures etc this:
USE tempdb;
GO
CREATE TABLE TableA(
ID INT PRIMARY KEY,
EntityType CHAR,
Value SMALLMONEY);
GO
INSERT INTO TableA(ID,EntityType,Value)
VALUES (1,'A',15.12),
(2,'A',44.11),
(3,'A',56.23),
(4,'B',12),
(5,'B',25.7),
(6,'B',45.32);
CREATE TABLE TableB (
ID INT PRIMARY KEY,
EntityType CHAR,
Value SMALLMONEY,
Band CHAR(5));
GO
INSERT INTO TableB(ID,EntityType,Value,Band)
VALUES
(1,'A',20,'Band1'),
(2,'A',30,'Band2'),
(3,'A',40,'Band3'),
(4,'A',60,'Band4'),
(5,'B',15,'Band1'),
(6,'B',40,'Band2'),
(7,'B',60,'Band3');
Because then folks here can copy and paste it into SSMS and run it and have a working example to start from. Okay, enough on the lecturing...
This is my best estimate... I'm missing something in the CROSS APPLY, I think. Could you post the correct answer as well? (Just makes it easier for everyone to verify that the solution they've come up with is right or not.)
SELECT a.ID
, a.EntityType
, a.Value
, x.Band
FROM TableA a
CROSS APPLY (SELECT TOP 1 Band
FROM TableB b
WHERE b.EntityType = a.EntityType
AND a.Value<=b.Value
ORDER BY b.Value) x
March 21, 2016 at 10:58 am
As a best estimate, that was a good effort! 😀
I altered the query to bring back a little more info to check if the answer was as hoped...
SELECT a.ID
, a.EntityType
, a.Value
, x.Value
, x.Band
FROM TableA a
CROSS APPLY (SELECT TOP 1 Value,Band
FROM TableB b
WHERE b.EntityType = a.EntityType
AND a.Value<=b.Value
ORDER BY b.Value ) x
Added in another value for luck, and
1A15.1220.00Band1
2A44.1160.00Band4
3A56.2360.00Band4
4B12.0015.00Band1
5B25.7040.00Band2
6B45.3260.00Band3
7A32.0040.00Band3
And that looks spot on to me. 😎
I obviously should have thought whether I needed the value to fall within a band which is greater or lesser than the value, but the above works, so I may leave it as is.
Thank you very much for working that out for me. And your comments on providing source example is duly noted. I've asked enough tricky questions in recent years to know better (even if I seldom visit).
🙂
Les
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply