Joining tables with different intervals

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Joining tables with different intervals

Author

Topic

Sweeper
Starting Member

1 Post

Posted - 2013-02-14 : 19:40:38

Hi

I'm trying to write a script to join geological down hole data based on the depth down hole. The issue is the data has different interval depths. I'm looking to join geology (intervals between 0.3m - 10m) and assay grades (0.5m-4m).

An example of the two tables are:
Geology
HoleID | From | To | Rock Type
DH001 | 0 | 0.3 | Sandstone
DH001 | 0.3 | 1.2 | Shale
DH001 | 1.2 | 3.3 | Sandstone
DH001 | 3.3 | 4.0 | Limestone

Assay
HoleID | From | To | Grade
DH001 | 0 | 0.5 | 630
DH001 | 0.5 | 1.0 | 520
DH001 | 1.0 | 2.0 | 340
DH001 | 2.0 | 3.0 | 160
DH001 | 3.0 | 4.0 | 180

I would like the export to look like this:
HoleID | From | To | Rock Type | Grade
DH001 | 0 | 0.3 | Sandstone | 630
DH001 | 0.3 | 0.5 | Shale | 630
DH001 | 0.5 | 1.0 | Shale | 520
DH001 | 1.0 | 1.2 | Shale | 340
DH001 | 1.2 | 2.0 | Sandstone | 340
DH001 | 2.0 | 3.0 | Sandstone | 160
DH001 | 3.0 | 3.3 | Sandstone | 180
DH001 | 3.3 | 4.0 | Limestone | 180

So I want to actually split both the Rock Type and the Grade if the intervals require it.

I've managed to be able to get as far as splitting the rock type and displaying the appropriate grade, but I can't get it to split the grade for the appropriate rock type at the same time. The sql code I've been working on is:

SELECT Assay.HoleID, Assay.[From], Assay.[To], Geology.RockType, Assay.Grade
FROM Assay LEFT OUTER JOIN Geology ON Assay.HoleID = Geology.HoleID
WHERE(Assay.[From] >= Geology.[From]) AND (Assay.[To] <= Geology.[To])

Thanks in advance for your help.
Trevor

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-15 : 07:16:17

If you post the data in a consumable format (i.e., something that one can copy and paste to an SSMS window to create test table and data and run queries against that), that makes it easy for someone to write a query and test. Without that a lot of time and efforts on the part of the person who wants to respond is required to create the data. I have created the consumable test data for you. I have to do some real work now, and haven't had a chance to read carefully enough to understand the problem you are trying to solve. So hopefully someone else will post a solution; if not I can give it a try when I come back.

-- CONSUMABLE SAMPLE DATA
CREATE TABLE #Geology
(HoleId VARCHAR(32), FromDepth FLOAT, ToDepth FLOAT, RockType VARCHAR(32));
CREATE TABLE #Assay
(HoleId VARCHAR(32), FromDepth FLOAT, ToDepth FLOAT, Grade INT);

INSERT INTO #Geology VALUES
('DH001','0','0.3','Sandstone'),
('DH001','0.3','1.2','Shale'),
('DH001','1.2','3.3','Sandstone'),
('DH001','3.3','4.0','Limestone')

INSERT INTO #Assay VALUES
('DH001','0','0.5','630'),
('DH001','0.5','1.0','520'),
('DH001','1.0','2.0','340'),
('DH001','2.0','3.0','160'),
('DH001','3.0','4.0','180');

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-15 : 08:04:06

Can you see if this will work for you? This uses the sample tables in my previous post

SELECT 
	g.HoleID,
	CASE WHEN g.FromDepth > a.FromDepth THEN g.FromDepth ELSE a.FromDepth END [From],
	CASE WHEN g.ToDepth > a.ToDepth THEN a.ToDepth ELSE g.ToDepth END [To],
	g.RockType,
	a.Grade
FROM
	#Geology g
	CROSS JOIN #Assay a
WHERE
	g.HoleId = a.HoleId
	AND g.FromDepth <=  a.ToDepth AND g.ToDepth >= a.FromDepth

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources