Posted by markc600 on 10/01/37 11:40

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

[Back to original message]