SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Joining tables with different intervals
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sweeper
Starting Member

Australia
1 Posts

Posted - 02/14/2013 :  19:40:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3657 Posts

Posted - 02/15/2013 :  07:16:17  Show Profile  Reply with Quote
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');

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3657 Posts

Posted - 02/15/2013 :  08:04:06  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000