AND DRILLINGDATE BETWEEN GEODETAILS.GEOLFROM AND GEODETAILS.GEOLTO

declare @DrillingDetail table (holeid varchar(10), drillingdate date, totaldrilldepth int)
insert into @DrillingDetail values('Uz-1', '20171025', 15)
insert into @DrillingDetail values('Uz-1', '20171026', 3)

declare @GeoInterval table (holeid varchar(10), geolFrom int, geolTo int, priority varchar(10));
insert into @Geointerval values('Uz-1', 0, 15, 'Al')
insert into @Geointerval values('Uz-1', 15, 18, 'Al')

-- это для просветления
select *, Total = SUM(TotalDrillDepth) OVER (ORDER BY CAST(DrillingDate as date) ROWS UNBOUNDED PRECEDING ) from @DrillingDetail;

-- ну а это - фсе
with t as (select *, Total = SUM(TotalDrillDepth) OVER (ORDER BY CAST(DrillingDate as date) ROWS UNBOUNDED PRECEDING ) from @DrillingDetail)
  select *
    from @Geointerval as g inner join t on g.holeid = t.holeid and  g.geolFrom < t.total and t.total <= g.geolTo
    order by drillingdate;