Re: Help with SQL/report
On Tue, 19 May 1998 18:49:42 GMT, bmoskovi@consrv.ca.gov wrote:
>Hello Informix users,
>
>I am an Informix SE user who needs some help generating a report. I have a
>table like this:
>
> lithology.official_name integer <------key fields
> top_depth real <-----/
> bottom_depth real /
> lith_type char(6) <---/
>
>I am trying to generate a report of all official_name that has the
>interval defined by the top_depth and bottom_depth intersecting the top_depth
>and bottom_depth of the next layer(s).
>
>For example:
>
>official_name top_depth bottom_depth lith_type
>----------------------------------------------------
>WELL1 0.0 6.0 SD
>WELL1 6.0 7.5 ML <--- this layer crosses
>WELL1 7.25 9.0 CL <--- this layer
>WELL1 9.0 14.0 SD
This might work (if your data is as I think it is):
select *
from lithology a
where exists
(select 1
from lithology b
where
(a.top_depth between b.top_depth and b.bottom_depth or
a.bottom_depth between b.top_depth and b.bottom_depth)
and a.lith_type != b.lith_type
)
Hope that helps,
Douglas Wilson