May 8, 2024 at 12:09 pm
After rereading the original post I now understand about the INTERVAL_THICKNESS, this query works using a case statement to calculate the INTERVAL_THICKNESS
;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,
CASE
WHEN dd.DEPTH_FROM >= fn.N and dd.DEPTH_TO <= fn.N + 1 then dd.DEPTH_TO - dd.DEPTH_FROM
WHEN dd.DEPTH_FROM >= fn.N and dd.DEPTH_TO > fn.N + 1 then fn.N + 1 - dd.DEPTH_FROM
WHEN dd.DEPTH_FROM <= fn.N and dd.DEPTH_TO <= fn.N + 1 then dd.DEPTH_TO - fn.N
WHEN dd.DEPTH_FROM <= fn.N and dd.DEPTH_TO >= fn.N + 1 then fn.N + 1 - fn.N
END 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 CASE
WHEN dd.DEPTH_FROM >= fn.N and dd.DEPTH_TO <= fn.N + 1 then dd.DEPTH_TO - dd.DEPTH_FROM
WHEN dd.DEPTH_FROM >= fn.N and dd.DEPTH_TO > fn.N + 1 then fn.N + 1 - dd.DEPTH_FROM
WHEN dd.DEPTH_FROM <= fn.N and dd.DEPTH_TO <= fn.N + 1 then dd.DEPTH_TO - fn.N
WHEN dd.DEPTH_FROM <= fn.N and dd.DEPTH_TO >= fn.N + 1 then fn.N + 1 - fn.N
END DESC) dd
ORDER BY 1, 2, 3
;
May 8, 2024 at 12:10 pm
Extra post to flip page
May 8, 2024 at 1:00 pm
To get the thickness calculation correct it needs to account for the boundary conditions. Instead of expanding across the entire sequence and then joining (with inequalities) to the resultant intervals this code expands the intervals of each downhole row. The interval is expanded across the number of integer boundary crossings (ceiling(DEPTH_TO)-floor(DEPTH_FROM)). The expanded intervals overlap such that the entire range of thicknesses is explicitly accounted for.
This query calculates the thicknesses of the downhole expanded sequence(s) (labelled as the "expanded floor" (because it's calculated that way)
with exp_cte as (
select dd.*, v.*, y.thickness, intvl.exp_floor
from #DOWNHOLE_DATA dd
cross apply (values (ceiling(DEPTH_TO)-floor(DEPTH_FROM))) v(int_xing)
cross apply dbo.fnTally(1, v.int_xing) fn
cross apply (values (floor(DEPTH_FROM)+fn.n)) intvl(exp_floor)
cross apply (values (iif(fn.n=1, iif(v.int_xing=1, DEPTH_TO-DEPTH_FROM, intvl.exp_floor-DEPTH_FROM), DEPTH_TO-(intvl.exp_floor-1)))) y(thickness))
select *
from exp_cte;
Within the overlapping downhole expanded intervals use ROW_NUMBER and PARTITION BY exp_floor and ORDER BY thicknesses
with exp_cte as (
select dd.*, v.*, y.thickness, intvl.exp_floor
from #DOWNHOLE_DATA dd
cross apply (values (ceiling(DEPTH_TO)-floor(DEPTH_FROM))) v(int_xing)
cross apply dbo.fnTally(1, v.int_xing) fn
cross apply (values (floor(DEPTH_FROM)+fn.n)) intvl(exp_floor)
cross apply (values (iif(fn.n=1, iif(v.int_xing=1, DEPTH_TO-DEPTH_FROM, intvl.exp_floor-DEPTH_FROM), DEPTH_TO-(intvl.exp_floor-1)))) y(thickness))
select *, row_number() over (partition by exp_floor order by thickness desc) rn
from exp_cte
order by PROJECT, HOLE_ID, DEPTH_FROM, exp_floor;
To get the final result you could select WHERE the row numbers equal 1
with
exp_cte as (
select dd.*, v.*, y.thickness, intvl.exp_floor
from #DOWNHOLE_DATA dd
cross apply (values (ceiling(DEPTH_TO)-floor(DEPTH_FROM))) v(int_xing)
cross apply dbo.fnTally(1, v.int_xing) fn
cross apply (values (floor(DEPTH_FROM)+fn.n)) intvl(exp_floor)
cross apply (values (iif(fn.n=1, iif(v.int_xing=1, DEPTH_TO-DEPTH_FROM, intvl.exp_floor-DEPTH_FROM), DEPTH_TO-(intvl.exp_floor-1)))) y(thickness)),
rn_cte as (
select *, row_number() over (partition by exp_floor order by thickness desc) rn
from exp_cte)
select *
from rn_cte
where rn=1
order by PROJECT, HOLE_ID, DEPTH_FROM, exp_floor;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Also a few other potential issues. If there are any gaps in the depth measurements this method won't fill them in. Also if there are equal thicknesses this method will randomly select one, maybe there's a preferred way to settle ties? The code could also be refactored so there's only 1 cte
with
exp_cte as (
select dd.*, v.*, y.thickness, intvl.exp_floor, row_number() over (partition by exp_floor order by thickness desc) rn
from #DOWNHOLE_DATA dd
cross apply (values (ceiling(DEPTH_TO)-floor(DEPTH_FROM))) v(int_xing)
cross apply dbo.fnTally(1, v.int_xing) fn
cross apply (values (floor(DEPTH_FROM)+fn.n)) intvl(exp_floor)
cross apply (values (iif(fn.n=1, iif(v.int_xing=1, DEPTH_TO-DEPTH_FROM, intvl.exp_floor-DEPTH_FROM), DEPTH_TO-(intvl.exp_floor-1)))) y(thickness))
select *
from exp_cte
where rn=1
order by PROJECT, HOLE_ID, DEPTH_FROM, exp_floor;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 9, 2024 at 10:42 am
Thanks @jonathan-2 AC Roberts & @steve-2 Collins for the suggestions.
I will just need to do some testing. I may have found a possible anomaly with the supplied data based on your results.
Just give me a day or two to look into it and report back.
May 17, 2024 at 3:20 am
Hi all,
Apologies for the delayed response. I have received feedback and it was confirmed that some of the example data I was provided had an anomaly. However, this was not present in the example data supplied in the initial script.
@steve-2, your solution seems to be working. As you mentioned the equal thicknesses could present an issue. Feedback from the users is that it should be the first occurrence, and it seems to be picking that up.
I'll mark yours as the solution. Thank you very much for your assistance on this one and thanks to everyone else for tackling this unusual problem.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply