You are here: Re: Getting closest matching values « MsSQL Server « IT news, forums, messages
Re: Getting closest matching values

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация