1

Topic: How to restrict the table on dates from other table

Hello all
Please prompt as me to be. There are 2 tables
The first (DrillingDetail) contains drilling date (DrillingDate) and a slit name (holeID), well and still any columns
The second (GeoInterval) contains a slit name (holeID), from (From), to (To), still columns (Value)
It is necessary to receive such table that intervals from the second table were displayed only for specific date DrillingDate
Here for example  a slit from October, 14 till October, 28th, I want to look only how many drilled on October, 25th, and my script produces a slit entirely. Help, please
select GEODETAILS.holeid, GEODETAILS.GEOLFROM, GEODETAILS.GEOLTO, drillingdate
from
GEODETAILS left join DRILLINGDETAIL
on
GEODETAILS.holeid = DRILLINGDETAIL.holeid
where DRILLINGDATE = ' 26-oct-2017'

2

Re: How to restrict the table on dates from other table

katish444;
If I correctly understood you, remove Left

3

Re: How to restrict the table on dates from other table

Did not work, I and inner join tried...

4

Re: How to restrict the table on dates from other table

katish444 wrote:

did not work, I and inner join tried...

Did not work "that"?
1. IT "worked"
[code]
select GEODETAILS.holeid, GEODETAILS.GEOLFROM, GEODETAILS.GEOLTO, drillingdate
from
GEODETAILS inner join DRILLINGDETAIL
on
GEODETAILS.holeid = DRILLINGDETAIL.holeid
--where DRILLINGDATE = ' 26-oct-2017'
[code]
2. Dates write  ' 20171026'
3. Also will be to you ...

5

Re: How to restrict the table on dates from other table

Did not work.
On the former produces intervals for all time of drilling, I do not know why
Matter is not in date record (all remaining the filter work with such format)
There is no at me a happiness.... And after all theoretically should be

6

Re: How to restrict the table on dates from other table

katish444;

select GEODETAILS.holeid, GEODETAILS.GEOLFROM, GEODETAILS.GEOLTO, drillingdate
from
GEODETAILS join DRILLINGDETAIL
on
GEODETAILS.holeid = DRILLINGDETAIL.holeid AND DRILLINGDATE BETWEEN GEODETAILS.GEOLFROM AND GEODETAILS.GEOLTO
where DRILLINGDATE = ' 20171026'

7

Re: How to restrict the table on dates from other table

iiyama;
DrillingDate - varchar, therefore it is necessary to write date as at me it is written.
Essence not in it. In the second tables intervals and a slit name, and in the first a slit name, date of drilling and an amount drilled for days, that is intervals from-to there are not present, therefore not so all is simple. That that I want turned out it is necessary to add drilled intervals. But I not absolutely understood as. Created the virtual table, and further how to be? The cycle any is necessary?

8

Re: How to restrict the table on dates from other table

katish444;
In that case from you scripts of creation of tables, filling by the data (test) and the sample of desirable result.

9

Re: How to restrict the table on dates from other table

iiyama wrote:

katish444;
In that case from you scripts of creation of tables, filling by the data (test) and the sample of desirable result.

http://www.sql.ru/forum/127456/rekomend … y-v-forume
Points 4 and 6.

10

Re: How to restrict the table on dates from other table

HoleID DrillingDate TotalDrillDepth
1 25-oct-2017 11
1 26-oct-2017 57
1 27-oct-2017 43
2 25-oct-2017 38
Here there is such table. How to make so that on demand for specific date it produced total TotalDrillDepth
That is for example for October, 27th it is drilled all 43+57+11

11

Re: How to restrict the table on dates from other table

katish444 wrote:

HoleID DrillingDate TotalDrillDepth
1 25-oct-2017 11
1 26-oct-2017 57
1 27-oct-2017 43
2 25-oct-2017 38
Here there is such table. How to make so that on demand for specific date it produced total TotalDrillDepth
That is for example for October, 27th it is drilled only 43+57+11

DrillingDate is simply arbitrary line?

12

Re: How to restrict the table on dates from other table

katish444;
' 26-oct-2017'
You will like to sort dates in
CAST (DRILLINGDATE as date) under the table at you transits?

13

Re: How to restrict the table on dates from other table

Pancake, the previous message absolutely not readably it turned out
The first table GeoInterval (holeid, geolFrom, geolTo, priority) all varchar
insert into Geointerval (Uz-1, 0, 15, Al)
insert into Geointerval (Uz-1, 15, 18, Al)
The second table DrillingDetail (holeid, drillingdate, totaldrilldepth) all varchar
insert into Geodetail (Uz-1, 25-oct-2017, 15)
insert into Geodetail (Uz-1, 26-oct-2017, 3)
I need to receive the table as Geointerval, but thus to have possibility to sort by drilling date.
To view with what on what interval  in this date

14

Re: How to restrict the table on dates from other table

katish444 wrote:

iiyama;
DrillingDate - varchar, therefore it is necessary to write date as at me it is written.
Essence not in it.

Essence in it.
While you do not learn to write date - dates - you will walk on a rake infinitely.

AND DRILLINGDATE BETWEEN GEODETAILS.GEOLFROM AND GEODETAILS.GEOLTO

How  to work on lines?
. If it does not reach you - better at once to train for a new profession in house managers.

15

Re: How to restrict the table on dates from other table

TaPaK, yes, of course, I use CAST
The problem simply not in it and I lowered superfluous overloadings for the publication on

16

Re: How to restrict the table on dates from other table

katish444;

SUM (TotalDrillDepth) OVER (ORDER BY CAST (DrillingDate as date) ROWS UNBOUNDED PRECEDING AND CURRENT ROW)

17

Re: How to restrict the table on dates from other table

katish444 wrote:

the Pancake, the previous message absolutely not readably it turned out
The first table GeoInterval (holeid, geolFrom, geolTo, priority) all varchar
insert into Geointerval (Uz-1, 0, 15, Al)
insert into Geointerval (Uz-1, 15, 18, Al)
The second table DrillingDetail (holeid, drillingdate, totaldrilldepth) all varchar
insert into Geodetail (Uz-1, 25-oct-2017, 15)
insert into Geodetail (Uz-1, 26-oct-2017, 3)
I need to receive the table as Geointerval, but thus to have possibility to sort by drilling date.
To view with what on what interval  in this date

It scoffs.
0, 15
And
25-oct-2017
How to correlate?

18

Re: How to restrict the table on dates from other table

aleks222, I write dates normally, I will always transform. The program in which I work, uses how I sent, when it is necessary for me, I use CAST
The problem after all at me not in writing of dates, and in that as to write me a cycle that the drilled meters added with each other. By the way, meters at me too the text, it not the error, is a DB singularity since all this field virtual,  it long to explain

19

Re: How to restrict the table on dates from other table

aleks222;
Look, when I will have a table which adds TotalDrillingDate to was specific the named date then this number and coincides with GeolTo, after all the maximum quantity  meters and is an interval "to"

20

Re: How to restrict the table on dates from other table

TaPaK;
Thanks, prompt current row is on what he refers? What continuation should be or as I do not understand it is a little

21

Re: How to restrict the table on dates from other table

katish444 wrote:

I need to receive the table as Geointerval, but thus to have possibility to sort by drilling date.
To view with what on what interval  in this date

I, apparently, started to understand:
geolFrom, geolTo is from what depth to what reached in what mine it is not known when.
drillingdate, totaldrilldepth is in what date in what mine how many transited for days of drilling.
But after all you do not have key of communication of these tables.

22

Re: How to restrict the table on dates from other table

TaPaK, changed your script to:
select SUM (CAST (TotalDrillDepth) OVER (ORDER BY CAST (DrillingDate as date) dat ROWS UNBOUNDED PRECEDING) from @Drilling_temp
Writes error Incorrect syntax near ' ROWS'.
What not so?

23

Re: How to restrict the table on dates from other table

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 ')
-- It for an enlightenment
select *, Total = SUM (TotalDrillDepth) OVER (ORDER BY CAST (DrillingDate as date) ROWS UNBOUNDED PRECEDING) from @DrillingDetail;
-- Well and it - 
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;

.  I offer  forever for laziness.

24

Re: How to restrict the table on dates from other table

katish444;
This restriction of a window, means till a current line. It is possible to change on, for example Rows between 1 preceding and following, i.e. for each current line we will consider the total of three lines (leaking, previous and following). But in that case there can be problems with productivity because of development of all lines of a frame.

25

Re: How to restrict the table on dates from other table

Slice;
Only on HoleID is a name of a slit, it is both there and there
Still if we add on TotalDrillDepth for current date - that this digit is mandatory coincides with GeolTo because how many meters  - so much and described