|
Home > Archive > Microsoft SQL Server forum > February 2006 > Getting closest matching values
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Getting closest matching values
|
|
| Hennie 2006-02-20, 7:23 am |
| I apologise if this is a stupid question, but I would appreciated any
help on this subject.
I want to create a view (VIEW_1 in example below) where I take numeric
values from a field in one table (DEPTH_FROM in TABLE_1) and find the
closest matching values from another field in another table (DEPTH_AT
in TABLE_2) - the higher matching value and the lower matching value.
TABLE_1
BH_ID DEPTH_FROM
B1 10.6
B1 14.2
B1 16.1
B1 17.0
TABLE_2
BH_ID DEPTH_AT
B1 9
B1 10
B1 11
B1 12
B1 13
B1 14
B1 15
B1 16
B1 17
B1 18
VIEW_1
BH_ID DEPTH_FROM DEPTH_LOWER DEPTH_HIGHER
B1 10.6 10
11
B1 14.2 14
15
B1 16.1 16
17
B1 17.0 17
17
Any assistance would be appreciated.
Regards,
Hennie
| |
| markc600@hotmail.com 2006-02-20, 7:23 am |
| Should give you what you want
CREATE TABLE TABLE_1(BH_ID CHAR(2), DEPTH_FROM DECIMAL(5,1))
INSERT INTO TABLE_1(BH_ID,DEPTH_
FROM) VALUES('B1',10.6)
INSERT INTO TABLE_1(BH_ID,DEPTH_
FROM) VALUES('B1',14.2)
INSERT INTO TABLE_1(BH_ID,DEPTH_
FROM) VALUES('B1',16.1)
INSERT INTO TABLE_1(BH_ID,DEPTH_
FROM) VALUES('B1',17.0)
CREATE TABLE TABLE_2(BH_ID CHAR(2), DEPTH_AT DECIMAL(5,1))
INSERT INTO TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',9)
INSERT INTO TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',10)
INSERT INTO TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',11)
INSERT INTO TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',12)
INSERT INTO TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',13)
INSERT INTO TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',14)
INSERT INTO TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',15)
INSERT INTO TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',16)
INSERT INTO TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',17)
INSERT INTO TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',18)
GO
CREATE VIEW DEPTH_RANGES
AS
SELECT a.BH_ID,
a.DEPTH_AT AS DEPTH_LOWER,
b.DEPTH_AT AS DEPTH_HIGHER
FROM TABLE_2 a
INNER JOIN TABLE_2 b ON b.BH_ID=a.BH_ID
AND b.DEPTH_AT=(SELECT MIN(c.DEPTH_AT)
FROM TABLE_2 c
WHERE c.BH_ID=a.BH_ID
AND c.DEPTH_AT>a.DEPTH_AT)
GO
CREATE VIEW VIEW_1
AS
SELECT a.BH_ID,
b.DEPTH_LOWER,
a.DEPTH_FROM,
b.DEPTH_HIGHER
FROM TABLE_1 a
INNER JOIN DEPTH_RANGES b ON b.BH_ID=a.BH_ID
AND a.DEPTH_FROM >= b.DEPTH_LOWER
AND a.DEPTH_FROM < b.DEPTH_HIGHER
GO
| |
| Hennie 2006-02-20, 9:23 am |
| Thanks Mark!
This helps a lot!
Regards,
Hennie
|
|
|
|
|