Multi row tally query with max value
-
Hello folks,
I would be very grateful if someone could assist me with this problem.
I have been having an issue getting this query to display correctly. This is basically some geological drillhole data being captured by depth intervals. A user request came in for having this displayed in single 1 meter intervals. A tally approach was first taken and the users seemed to be a happy. They have comeback with a couple of minor issues, but having difficulties getting the logic to work in this context.
The data looks like this:
The output we are attempting looks like this:
So far looks good to the eye, but in detail this is the issue. The user states that the output below is picking up the incorrect value for LITH1
According to the user rules this should be the largest unit value within the meter (tally in this case). Meaning the largest interval difference:
I have tried aggregating with a Max value for max interval, but struggling to make it work in this context. I seem to be going in circles.
I have attached a script which will create all the tables , function, insert the test data. I have also included and example output.
Query for raw data:
select *, (DEPTH_TO-DEPTH_FROM) AS INTERVAL_THICKNESS
from DOWNHOLE_DATA
Query:
SELECT PROJECT, HOLE_ID,NEW_DEPTH_FROM ,NEW_DEPTH_TO, LITH1
from(
select dt.PROJECT, dt.HOLE_ID,depth_from,depth_to, v.dpth_from_rnd+fn.n-1 NEW_DEPTH_FROM,v.dpth_from_rnd+fn.n NEW_DEPTH_TO, LITH1, N
,(SELECT END_DEPTH FROM HOLE_DATA WHERE PROJECT = dt.PROJECT AND HOLE_ID = dt.HOLE_ID) AS LITH_END_DEPTH
from DOWNHOLE_DATA dt
cross apply (values (round(DEPTH_TO, 0), round(DEPTH_FROM, 0))) v(dpth_to_rnd, dpth_from_rnd)
cross apply dbo.fnTally(1, (v.dpth_to_rnd-v.dpth_from_rnd)) fn
WHERE HOLE_ID = 'HOLE_001'
)LITHOLOGY
Example output Data Query:
SELECT *
FROM OUTPUT_EXAMPLE
ORDER BY PROJECT, HOLE_ID,DEPTH_FROM,DEPTH_TO
Any help would be greatly appreciated.
Thanks!
-
Many people are hesitant to open random files from the Internet. Here is a link on Forum Etiquette: How to post data/code on a forum to get the best help. That link should be updated to use Table Value Constructors which were introduced after this article was written.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Not sure exactly what you want, but maybe something like this?
Create some data:
-- Creating the table
CREATE TABLE DrillingData (
PROJECT NVARCHAR(50),
HOLE_ID NVARCHAR(50),
DEPTH_FROM FLOAT,
DEPTH_TO FLOAT,
LITH NVARCHAR(10),
INTERVAL_THICKNESS FLOAT
);
-- Inserting the data
INSERT INTO DrillingData (PROJECT, HOLE_ID, DEPTH_FROM, DEPTH_TO, LITH, INTERVAL_THICKNESS) VALUES
('TEST', 'HOLE_001', 0.000, 8.950, 'MEH', 8.950),
('TEST', 'HOLE_001', 8.950, 13.300, 'MIGW', 4.350),
('TEST', 'HOLE_001', 13.300, 14.000, 'MEH', 0.700),
('TEST', 'HOLE_001', 14.000, 15.400, 'MIGP', 1.400),
('TEST', 'HOLE_001', 15.400, 25.000, 'MEH', 9.600),
('TEST', 'HOLE_001', 25.000, 25.950, 'MIGP', 0.950),
('TEST', 'HOLE_001', 25.950, 30.600, 'MEH', 4.650),
('TEST', 'HOLE_001', 30.600, 31.900, 'GAM', 1.300),
('TEST', 'HOLE_001', 31.900, 33.200, 'MEH', 1.300),
('TEST', 'HOLE_001', 33.200, 36.000, 'GAM', 2.800),
('TEST', 'HOLE_001', 36.000, 37.750, 'MEH', 1.750),
('TEST', 'HOLE_001', 37.750, 38.700, 'MIGP', 0.950),
('TEST', 'HOLE_001', 38.700, 40.400, 'GAM', 1.700),
('TEST', 'HOLE_001', 40.400, 45.400, 'MEH', 5.000);Query data
;WITH fn as
(
select N
from dbo.fnTally(0, 300) fn
)
SELECT dd.PROJECT, dd.HOLE_ID, fn.N + 0.0 DEPTH_FROM,
fn.N + 1.0 DEPTH_TO,
dd.LITH,
dd.DEPTH_FROM,
dd.DEPTH_TO
FROM DrillingData dd
INNER JOIN fn
ON (dd.DEPTH_FROM <= fn.N + 0.0
AND dd.DEPTH_TO >= fn.N + 1.0)
ORDER BY 1, 2, 3
; - Jonathan AC Roberts wrote:
Not sure exactly what you want, but maybe something like this?
Create some data:
-- Creating the table
CREATE TABLE DrillingData (
PROJECT NVARCHAR(50),
HOLE_ID NVARCHAR(50),
DEPTH_FROM FLOAT,
DEPTH_TO FLOAT,
LITH NVARCHAR(10),
INTERVAL_THICKNESS FLOAT
);
-- Inserting the data
INSERT INTO DrillingData (PROJECT, HOLE_ID, DEPTH_FROM, DEPTH_TO, LITH, INTERVAL_THICKNESS) VALUES
('TEST', 'HOLE_001', 0.000, 8.950, 'MEH', 8.950),
('TEST', 'HOLE_001', 8.950, 13.300, 'MIGW', 4.350),
('TEST', 'HOLE_001', 13.300, 14.000, 'MEH', 0.700),
('TEST', 'HOLE_001', 14.000, 15.400, 'MIGP', 1.400),
('TEST', 'HOLE_001', 15.400, 25.000, 'MEH', 9.600),
('TEST', 'HOLE_001', 25.000, 25.950, 'MIGP', 0.950),
('TEST', 'HOLE_001', 25.950, 30.600, 'MEH', 4.650),
('TEST', 'HOLE_001', 30.600, 31.900, 'GAM', 1.300),
('TEST', 'HOLE_001', 31.900, 33.200, 'MEH', 1.300),
('TEST', 'HOLE_001', 33.200, 36.000, 'GAM', 2.800),
('TEST', 'HOLE_001', 36.000, 37.750, 'MEH', 1.750),
('TEST', 'HOLE_001', 37.750, 38.700, 'MIGP', 0.950),
('TEST', 'HOLE_001', 38.700, 40.400, 'GAM', 1.700),
('TEST', 'HOLE_001', 40.400, 45.400, 'MEH', 5.000);Query data
;WITH fn as
(
select N
from dbo.fnTally(0, 300) fn
)
SELECT dd.PROJECT, dd.HOLE_ID, fn.N + 0.0 DEPTH_FROM,
fn.N + 1.0 DEPTH_TO,
dd.LITH,
dd.DEPTH_FROM,
dd.DEPTH_TO
FROM DrillingData dd
INNER JOIN fn
ON (dd.DEPTH_FROM <= fn.N + 0.0
AND dd.DEPTH_TO >= fn.N + 1.0)
ORDER BY 1, 2, 3
;dbo.fnTallyis a UDF. If your solution depends on a UDF, you should at least give the definition of the UDF. Here is code you can use to replace the UDF.WITH c AS ( SELECT n FROM (VALUES(0), (0), (0), (0), (0), (0), (0)) t(n))
, fn AS
(
SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) AS n
FROM c AS c1
CROSS JOIN c AS c2
CROSS JOIN c AS c3
)The depth data should start at 0, not 1. So you need to update your code to start at the correct depth.
Your join on the intervals is incorrect. When comparing intervals, the natural tendency is to compare starts with starts and ends with ends. This natural tendency is incorrect. You need to compare the start of each with the end of the other. Then you need to consider whether your intervals are open (don't include the end points), closed (do include the end points), or half-closed (include one end point, but not the other). Because you have used the incorrect joins, you are missing depths 8-9, 13-14, 15-16, 25-26, 30-31, 31-32, 33-34, 37-38, 38-39, 40-41, and 45-46.
My initial solution produces two values for some of the depths. I'm not sure what the criteria are for choosing which value to display, so my solution is incomplete. Here is my solution. NOTE: I changed your permanent table to a temp table.
WITH c AS ( SELECT n FROM (VALUES(0), (0), (0), (0), (0), (0), (0)) t(n))
, t AS
(
SELECT 1.0 *ROW_NUMBER() OVER(ORDER BY @@VERSION) AS rn
FROM c AS c1
CROSS JOIN c AS c2
CROSS JOIN c AS c3
)
, depths AS
(
SELECT rn-1 AS Depth_From
, rn AS Depth_To
FROM t
)
SELECT dd.PROJECT
, dd.HOLE_ID
, d.Depth_From
, d.Depth_To
, dd.LITH
, dd.DEPTH_FROM
, dd.DEPTH_TO
FROM #DrillingData AS dd
INNER JOIN depths AS d
ON dd.DEPTH_FROM < d.Depth_To
AND d.Depth_From < dd.DEPTH_TODrew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen wrote:Jonathan AC Roberts wrote:
Not sure exactly what you want, but maybe something like this?
Create some data:
-- Creating the table
CREATE TABLE DrillingData (
PROJECT NVARCHAR(50),
HOLE_ID NVARCHAR(50),
DEPTH_FROM FLOAT,
DEPTH_TO FLOAT,
LITH NVARCHAR(10),
INTERVAL_THICKNESS FLOAT
);
-- Inserting the data
INSERT INTO DrillingData (PROJECT, HOLE_ID, DEPTH_FROM, DEPTH_TO, LITH, INTERVAL_THICKNESS) VALUES
('TEST', 'HOLE_001', 0.000, 8.950, 'MEH', 8.950),
('TEST', 'HOLE_001', 8.950, 13.300, 'MIGW', 4.350),
('TEST', 'HOLE_001', 13.300, 14.000, 'MEH', 0.700),
('TEST', 'HOLE_001', 14.000, 15.400, 'MIGP', 1.400),
('TEST', 'HOLE_001', 15.400, 25.000, 'MEH', 9.600),
('TEST', 'HOLE_001', 25.000, 25.950, 'MIGP', 0.950),
('TEST', 'HOLE_001', 25.950, 30.600, 'MEH', 4.650),
('TEST', 'HOLE_001', 30.600, 31.900, 'GAM', 1.300),
('TEST', 'HOLE_001', 31.900, 33.200, 'MEH', 1.300),
('TEST', 'HOLE_001', 33.200, 36.000, 'GAM', 2.800),
('TEST', 'HOLE_001', 36.000, 37.750, 'MEH', 1.750),
('TEST', 'HOLE_001', 37.750, 38.700, 'MIGP', 0.950),
('TEST', 'HOLE_001', 38.700, 40.400, 'GAM', 1.700),
('TEST', 'HOLE_001', 40.400, 45.400, 'MEH', 5.000);Query data
;WITH fn as
(
select N
from dbo.fnTally(0, 300) fn
)
SELECT dd.PROJECT, dd.HOLE_ID, fn.N + 0.0 DEPTH_FROM,
fn.N + 1.0 DEPTH_TO,
dd.LITH,
dd.DEPTH_FROM,
dd.DEPTH_TO
FROM DrillingData dd
INNER JOIN fn
ON (dd.DEPTH_FROM <= fn.N + 0.0
AND dd.DEPTH_TO >= fn.N + 1.0)
ORDER BY 1, 2, 3
;dbo.fnTallyis a UDF. If your solution depends on a UDF, you should at least give the definition of the UDF. Here is code you can use to replace the UDF.WITH c AS ( SELECT n FROM (VALUES(0), (0), (0), (0), (0), (0), (0)) t(n))
, fn AS
(
SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) AS n
FROM c AS c1
CROSS JOIN c AS c2
CROSS JOIN c AS c3
)The depth data should start at 0, not 1. So you need to update your code to start at the correct depth.
Your join on the intervals is incorrect. When comparing intervals, the natural tendency is to compare starts with starts and ends with ends. This natural tendency is incorrect. You need to compare the start of each with the end of the other. Then you need to consider whether your intervals are open (don't include the end points), closed (do include the end points), or half-closed (include one end point, but not the other). Because you have used the incorrect joins, you are missing depths 8-9, 13-14, 15-16, 25-26, 30-31, 31-32, 33-34, 37-38, 38-39, 40-41, and 45-46.
My initial solution produces two values for some of the depths. I'm not sure what the criteria are for choosing which value to display, so my solution is incomplete. Here is my solution. NOTE: I changed your permanent table to a temp table.
WITH c AS ( SELECT n FROM (VALUES(0), (0), (0), (0), (0), (0), (0)) t(n))
, t AS
(
SELECT 1.0 *ROW_NUMBER() OVER(ORDER BY @@VERSION) AS rn
FROM c AS c1
CROSS JOIN c AS c2
CROSS JOIN c AS c3
)
, depths AS
(
SELECT rn-1 AS Depth_From
, rn AS Depth_To
FROM t
)
SELECT dd.PROJECT
, dd.HOLE_ID
, d.Depth_From
, d.Depth_To
, dd.LITH
, dd.DEPTH_FROM
, dd.DEPTH_TO
FROM #DrillingData AS dd
INNER JOIN depths AS d
ON dd.DEPTH_FROM < d.Depth_To
AND d.Depth_From < dd.DEPTH_TODrew
I'm not the OP. The UDF (dbo.fnTally) is a well know one by Jeff Moden and the OP is using this in his original post.
The depth data does start at 0, not 1, as the first parameter to fnTally is 0.
- Jonathan AC Roberts wrote:
I'm not the OP. The UDF (dbo.fnTally) is a well know one by Jeff Moden and the OP is using this in his original post.
The depth data does start at 0, not 1, as the first parameter to fnTally is 0.
I saw that the UDF was in the original post after I had posted. Even so, some places restrict the creation of UDFs, so having an alternative is worthwhile.
I saw that 0-1 was missing from your results, and assumed that it was the result of the wrong starting point. It's probably because of your joins.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen wrote:Jonathan AC Roberts wrote:
I'm not the OP. The UDF (dbo.fnTally) is a well know one by Jeff Moden and the OP is using this in his original post.
The depth data does start at 0, not 1, as the first parameter to fnTally is 0.
I saw that the UDF was in the original post after I had posted. Even so, some places restrict the creation of UDFs, so having an alternative is worthwhile.
I saw that 0-1 was missing from your results, and assumed that it was the result of the wrong starting point. It's probably because of your joins.
Drew
I don't think 0-1 was missing from my results:
I've got to say I'm not really sure what the OP wants so just put that query in as a starting point.
-
With this query:
;WITH fn as
(
select N
from dbo.fnTally(0, 300) fn
)
SELECT dd.PROJECT, dd.HOLE_ID, fn.N + 0.0 DEPTH_FROM,
fn.N + 1.0 DEPTH_TO,
dd.LITH1,
dd.DEPTH_TO - dd.DEPTH_FROM AS INTERVAL_THICKNESS,
dd.DEPTH_FROM,
dd.DEPTH_TO
FROM [DOWNHOLE_DATA] dd
INNER JOIN fn
ON dd.DEPTH_FROM <= fn.N
AND dd.DEPTH_TO > fn.N
ORDER BY 1, 2, 3
;I get these results:
Which I think might be what you are looking for?
-
This was removed by the editor as SPAM
-
I've grown to trust more in zip files being benign and like them a whole lot better than having to go to some other site to get them. Still, there are a lot of people that don't trust things.
I opened up your zip file. Nicely done. I'll take a look and see if I can duplicate the anomaly that you're seeing, to start with.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok... I had a look. First, I've got to say I don't know why supposed scientists are willing to lose data by rounding it. Whatever.
Looking back at this thread, Jonathan and Drew have both posted "Tries". Without me (or anyone else) having to run all their code to see if it works for you, did any of their code work for you or are you still having issues? If the code from one of the posts did work for you, please mark it as the answer... some of us would like to see what others did to fix it.
Thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi
@jonathan-2 AC Roberts,
Thanks for your help. I tried it, but this one is suppressing the rows. I am expecting530 and 242 is being returned.
I have tried the other suggestions, but having the sample issues where 530 is not being returned.
EDIT:
Apologies Jonathan. I forgot to change the tally value from 300 to 530. However it looks like it is locking on the LITH1 of the 1 meter tally. I need somehow to instruct if there are multiple LITH1 rows within the meter, then grab the one with the largest INTERVAL_THICKNESS.
So within the 13 - 14 interval output, I am expecting the largest LITH1 value interval of 0.7 is to be picked up and used in the query output.
Sounds confusing when I say the largest interval which naturally I would think it is the previous interval, however that only crosses over into the next. The key word is within the 1 meter which is 0.7
Somehow I am thinking of a way to grab that max value of the interval thickness and have it lock onto that one.
-
@drew.allen
Thanks for your response! The depth can also start at other values. Not always necessarily 0.
-
Hi @jeff Moden,
Many thanks for your reply. Also thanks for sharing your tally function. This has helped me solve many problems in the past.
My user base consists of geologists, which I have to say always come up with some odd requests. This particular request was more used for display purposes. The interest is in the larger unit which will be overlayed with the lab results which are taken in 1 meter sample interval in a 3D modelling software. So the largest thickness within the one meter intervals are the data point of interest.
Unfortunately the other suggestions did not work for me. The closest one is the one I published above.
SELECT PROJECT, HOLE_ID,NEW_DEPTH_FROM ,NEW_DEPTH_TO, LITH1
from(
select dt.PROJECT, dt.HOLE_ID,depth_from,depth_to, v.dpth_from_rnd+fn.n-1 NEW_DEPTH_FROM,v.dpth_from_rnd+fn.n NEW_DEPTH_TO, LITH1, N
,(SELECT END_DEPTH FROM HOLE_DATA WHERE PROJECT = dt.PROJECT AND HOLE_ID = dt.HOLE_ID) AS LITH_END_DEPTH
from DOWNHOLE_DATA dt
cross apply (values (round(DEPTH_TO, 0), round(DEPTH_FROM, 0))) v(dpth_to_rnd, dpth_from_rnd)
cross apply dbo.fnTally(1, (v.dpth_to_rnd-v.dpth_from_rnd)) fn
WHERE HOLE_ID = 'HOLE_001'
)LITHOLOGY
This is a good test query where there should be no difference between the query above and the example supplied by the users:
SELECT PROJECT, HOLE_ID,NEW_DEPTH_FROM ,NEW_DEPTH_TO, LITH1
from(
select dt.PROJECT, dt.HOLE_ID,depth_from,depth_to, v.dpth_from_rnd+fn.n-1 NEW_DEPTH_FROM,v.dpth_from_rnd+fn.n NEW_DEPTH_TO, LITH1, N
,(SELECT END_DEPTH FROM HOLE_DATA WHERE PROJECT = dt.PROJECT AND HOLE_ID = dt.HOLE_ID) AS LITH_END_DEPTH
from DOWNHOLE_DATA dt
cross apply (values (round(DEPTH_TO, 0), round(DEPTH_FROM, 0))) v(dpth_to_rnd, dpth_from_rnd)
cross apply dbo.fnTally(1, (v.dpth_to_rnd-v.dpth_from_rnd)) fn
WHERE HOLE_ID = 'HOLE_001'
)LITHOLOGY
EXCEPT
select *
from OUTPUT_EXAMPLE
If no rows are returned then that would be the correct solution.
On another note. I am seeing my replies are not looking right. Sorry it has been a while since I've been on here. Let me know if I am doing something wrong.
- ibbo14 wrote:
However it looks like it is locking on the LITH1 of the 1 meter tally. I need somehow to instruct if there are multiple LITH1 rows within the meter, then grab the one with the largest INTERVAL_THICKNESS.
So within the 13 - 14 interval output, I am expecting the largest LITH1 value interval of 0.7 is to be picked up and used in the query output.
Sounds confusing when I say the largest interval which naturally I would think it is the previous interval, however that only crosses over into the next. The key word is within the 1 meter which is 0.7
Somehow I am thinking of a way to grab that max value of the interval thickness and have it lock onto that one.
Try this:
;WITH fn as
(
select N
from dbo.fnTally(0, 600) fn
)
SELECT dd.PROJECT,
dd.HOLE_ID,
fn.N DEPTH_FROM,
fn.N + 1 DEPTH_TO,
dd.LITH1,
dd.DEPTH_TO - dd.DEPTH_FROM AS INTERVAL_THICKNESS,
dd.DEPTH_FROM,
dd.DEPTH_TO
FROM fn
CROSS APPLY (SELECT TOP(1) *
FROM [DOWNHOLE_DATA] dd
WHERE dd.DEPTH_FROM <= convert(decimal, fn.N + 1)
AND dd.DEPTH_TO > convert(decimal, fn.N)
ORDER BY dd.DEPTH_TO - dd.DEPTH_FROM DESC) dd
ORDER BY 1, 2, 3
;Not sure what you mean about the largest INTERVALE_THICKNESS?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply