1

Topic: To remove counterparts and to take initial and finite date, but the line can repeat

Children, prompt idea how to make.
There are lines, a difference at them in dates from to.
How to remove counterparts there is a period goes in sequence.

2

Re: To remove counterparts and to take initial and finite date, but the line can repeat

lag (), lead (), over ()

3

Re: To remove counterparts and to take initial and finite date, but the line can repeat

wrote:

lag (), lead (), over ()

Forgot to specify the server 2008 R

4

Re: To remove counterparts and to take initial and finite date, but the line can repeat

In such setting - grouping on first three fields, min - on the first date, max - on the second.

5

Re: To remove counterparts and to take initial and finite date, but the line can repeat

iii2 wrote:

In such setting - grouping on first three fields, min - on the first date, max - on the second.

If the task could so be solved, I here would not write.

6

Re: To remove counterparts and to take initial and finite date, but the line can repeat

Focha;
a group invariant . ROW_NUMBER to itself it is quite accessible from 2005 version of the server.

7

Re: To remove counterparts and to take initial and finite date, but the line can repeat

Focha wrote:

it is passed...
If the task could so be solved, I here would not write.

Write the task distinctly. Counterparts on which fields need to be removed? What is "the period goes in sequence"?

8

Re: To remove counterparts and to take initial and finite date, but the line can repeat

  - Eh wrote:

Focha;
a group invariant . ROW_NUMBER to itself it is quite accessible from 2005 version of the server.

And? Than to me helps ROW_NUMBER?

9

Re: To remove counterparts and to take initial and finite date, but the line can repeat

Focha;
Under links resemble-esteem. There all is written.

10

Re: To remove counterparts and to take initial and finite date, but the line can repeat

Whence old_retail_price 6390?

11

Re: To remove counterparts and to take initial and finite date, but the line can repeat

[V. Liseev] it is passed...
Write the task distinctly. Counterparts on which fields need to be removed? What is "the period goes in sequence"? [/quote]
On a picture it is visible the first 3 lines differ only the period which goes in sequence, I would like to remove a line where the article, the price leaking, the price old dates coincide also are serial, all would be easy, if too (articles/prices) were not doubled during other period. 5 line

12

Re: To remove counterparts and to take initial and finite date, but the line can repeat

[V. Liseev] Whence old_retail_price 6390? [/quote]
Thanks that you are very attentive. Did an example, and forgot to correct the price.

13

Re: To remove counterparts and to take initial and finite date, but the line can repeat

Focha wrote:

it is passed...
On a picture it is visible the first 3 lines differ only the period which goes in sequence, I would like to remove a line where the article, the price leaking, the price old dates coincide also are serial, all would be easy, if too (articles/prices) were not doubled during other period. 5 line

to Enumerate a line by means of ROW_NUMBER (), thus sorting by the first date or on the second, without a difference. So there will be a concept "a last/following line".

SELECT ROW_NUMBER () OVER (ORDER BY date_from) RowNumber, *
INTO #tmp
FROM table

Then to find a line where "the article, the price leaking, the price old" differ from the last line though in one field or the last line misses.

FROM
#tmp this
LEFT JOIN #tmp prev ON prev. RowNumber = this. RowNumber - 1
WHERE prev. RowNumber IS NULL OR prev.... <> this.... (Here fields on which it is banked)

14

Re: To remove counterparts and to take initial and finite date, but the line can repeat

[V. Liseev] it is passed...
To enumerate a line by means of ROW_NUMBER (), thus sorting by the first date or on the second, without a difference. So there will be a concept "a last/following line".

SELECT ROW_NUMBER () OVER (ORDER BY date_from) RowNumber, *
INTO #tmp
FROM table

Then to find a line where "the article, the price leaking, the price old" differ from the last line though in one field or the last line misses.

FROM
#tmp this
LEFT JOIN #tmp prev ON prev. RowNumber = this. RowNumber - 1
WHERE prev. RowNumber IS NULL OR prev.... <> this.... (Here fields on which it is banked)

[/quote]
Understood idea, now I will try.

15

Re: To remove counterparts and to take initial and finite date, but the line can repeat

[V. Liseev] it is passed...
To enumerate a line by means of ROW_NUMBER (), thus sorting by the first date or on the second, without a difference. So there will be a concept "a last/following line".

SELECT ROW_NUMBER () OVER (ORDER BY date_from) RowNumber, *
INTO #tmp
FROM table

Then to find a line where "the article, the price leaking, the price old" differ from the last line though in one field or the last line misses.

FROM
#tmp this
LEFT JOIN #tmp prev ON prev. RowNumber = this. RowNumber - 1
WHERE prev. RowNumber IS NULL OR prev.... <> this.... (Here fields on which it is banked)

[/quote]
Thanks, for idea, this that that are necessary. smile

16

Re: To remove counterparts and to take initial and finite date, but the line can repeat

Focha wrote:

it is passed...
Thanks, for idea, this that that are necessary. smile

it is possible to do without the separate temporary table if numbering of lines to push in the generalized tabular expression .

17

Re: To remove counterparts and to take initial and finite date, but the line can repeat

[V. Liseev] it is passed...
It is possible to do without the separate temporary table if numbering of lines to push in the generalized tabular expression . [/quote]
Dmitry, yes I and do. The temporary table to check more conveniently.
And how to remove if a line 3 times repeats.
At me while it turns out to receive that that is necessary through the cursor.

18

Re: To remove counterparts and to take initial and finite date, but the line can repeat

Focha wrote:

it is passed...
Dmitry, yes I and do. The temporary table to check more conveniently.
And how to remove if a line 3 times repeats.
At me while it turns out to receive that that is necessary through the cursor.

Idea in deducing a line only in the event that it differs from the previous. Not important, how many time it repeats.

19

Re: To remove counterparts and to take initial and finite date, but the line can repeat

[V. Liseev] it is passed...
The idea in deducing a line only in the event that it differs from the previous. Not important, how many time it repeats. [/quote]
deduced, and further? I sort out the cursor, can eat what that other approach?

20

Re: To remove counterparts and to take initial and finite date, but the line can repeat

Focha wrote:

  deduced, and further? I sort out the cursor, can eat what that other approach?

a subquery, table connection on itself with shift on your "numbering"...

21

Re: To remove counterparts and to take initial and finite date, but the line can repeat

Focha wrote:

it is passed...
deduced, and further? I sort out the cursor, can eat what that other approach?

And what you further that want?

22

Re: To remove counterparts and to take initial and finite date, but the line can repeat

Focha wrote:

it is passed...
deduced, and further? I sort out the cursor, can eat what that other approach?

In an initial post was only about  counterparts. We do not know that you want further.

23

Re: To remove counterparts and to take initial and finite date, but the line can repeat

Dear friends.
Thanks for your ideas, search by the cursor appeared enough (at once did not think, and Dmitry's idea prompted to me)
178 000 lines, were reduced to 120 000 ~30 %.

24

Re: To remove counterparts and to take initial and finite date, but the line can repeat

All thanks for review of a new series of log ""

25

Re: To remove counterparts and to take initial and finite date, but the line can repeat

TaPaK wrote:

all thanks for review of a new series of log ""

I regret that you read such logs.