1

Topic: Cursors cycles consultation

Good afternoon, colleagues, render, please, assistance.
Essence in that there is a label in which id and 3 dates. It is necessary  so that if date 3 empty we take date 3 of the next line etc. the last line should contain date 2 as the closing.
By means of analytic functions it was not possible to achieve result, decided to try by means of the cursor. But earlier did not use and somehow not so works. Prompt, whether probably to do without the cursor, also it is necessary to consider, what in the table it is a lot of records within the limits of everyone ID how it probably to consider?
I.e. by the example resulted more low should it turns out next lines:
1, 2013-02-22 00:00:00.000, 2013-03-01 00:00:00.000
1, 2013-03-22 00:00:00.000, 2013-04-30 00:00:00.000
1, 2013-06-22 00:00:00.000, 2013-07-12 00:00:00.000
1, 2013-07-22 00:00:00.000, 2013-08-14 00:00:00.000
1, 2013-09-22 00:00:00.000, 2013-10-28 00:00:00.000
1, 2013-12-22 00:00:00.000, 2014-02-13 00:00:00.000
Etc........
And a placenta a line:
1, 2015-03-22 00:00:00.000, 2015-08-21 00:00:00.000

create table dbo.test (
id int;
df datetime, dt datetime);
declare some_cursor cursor
for
(select 1 as id, ' 2013-02-22 00:00:00.000 ' as df, ' 2013-03-21 00:00:00.000 ' as dt, ' 2013-03-01 00:00:00.000 ' as fd union
select 1 as id, ' 2013-03-22 00:00:00.000 ' as df, ' 2013-04-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2013-04-22 00:00:00.000 ' as df, ' 2013-05-21 00:00:00.000 ' as dt, ' 2013-04-30 00:00:00.000 ' as fd union all
select 1 as id, ' 2013-06-22 00:00:00.000 ' as df, ' 2013-07-21 00:00:00.000 ' as dt, ' 2013-07-12 00:00:00.000 ' as fd union all
select 1 as id, ' 2013-07-22 00:00:00.000 ' as df, ' 2013-08-21 00:00:00.000 ' as dt, ' 2013-08-14 00:00:00.000 ' as fd union all
select 1 as id, ' 2013-09-22 00:00:00.000 ' as df, ' 2013-10-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2013-10-22 00:00:00.000 ' as df, ' 2013-11-21 00:00:00.000 ' as dt, ' 2013-10-28 00:00:00.000 ' as fd union all
select 1 as id, ' 2013-12-22 00:00:00.000 ' as df, ' 2014-01-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2014-01-22 00:00:00.000 ' as df, ' 2014-02-21 00:00:00.000 ' as dt, ' 2014-02-13 00:00:00.000 ' as fd union all
select 1 as id, ' 2014-03-22 00:00:00.000 ' as df, ' 2014-04-21 00:00:00.000 ' as dt, ' 2014-04-18 00:00:00.000 ' as fd union all
select 1 as id, ' 2014-05-22 00:00:00.000 ' as df, ' 2014-06-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2014-06-22 00:00:00.000 ' as df, ' 2014-07-21 00:00:00.000 ' as dt, ' 2014-06-26 00:00:00.000 ' as fd union all
select 1 as id, ' 2014-07-22 00:00:00.000 ' as df, ' 2014-08-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2014-08-22 00:00:00.000 ' as df, ' 2014-09-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2014-09-22 00:00:00.000 ' as df, ' 2014-10-21 00:00:00.000 ' as dt, ' 2014-09-26 00:00:00.000 ' as fd union all
select 1 as id, ' 2014-10-22 00:00:00.000 ' as df, ' 2014-11-21 00:00:00.000 ' as dt, ' 2014-11-21 00:00:00.000 ' as fd union all
select 1 as id, ' 2014-11-22 00:00:00.000 ' as df, ' 2014-12-21 00:00:00.000 ' as dt, ' 2014-12-20 00:00:00.000 ' as fd union all
select 1 as id, ' 2014-12-22 00:00:00.000 ' as df, ' 2015-01-21 00:00:00.000 ' as dt, ' 2015-01-20 00:00:00.000 ' as fd union all
select 1 as id, ' 2015-01-22 00:00:00.000 ' as df, ' 2015-02-21 00:00:00.000 ' as dt, ' 2015-02-21 00:00:00.000 ' as fd union all
select 1 as id, ' 2015-02-22 00:00:00.000 ' as df, ' 2015-03-21 00:00:00.000 ' as dt, ' 2015-03-21 00:00:00.000 ' as fd union all
select 1 as id, ' 2015-03-22 00:00:00.000 ' as df, ' 2015-04-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2015-04-22 00:00:00.000 ' as df, ' 2015-05-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2015-05-22 00:00:00.000 ' as df, ' 2015-06-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2015-06-22 00:00:00.000 ' as df, ' 2015-07-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2015-07-22 00:00:00.000 ' as df, ' 2015-08-21 00:00:00.000 ' as dt, ' NULL ' as fd
)
open some_cursor
declare @int_var int, @df datetime, @dt datetime, @fd datetime, @dff datetime
fetch next from some_cursor INTO @int_var, @df, @dt, @fd
while @@ FETCH_STATUS = 0
begin
if @fd is not null
SELECT @int_var, @df, @fd
INSERT INTO dbo.test (id, df, dt) VALUES (@int_var, @df, @fd)
set @dff = @df
fetch next from some_cursor INTO @int_var, @df, @dt, @fd
SELECT @int_var, @dff, @fd
INSERT INTO dbo.test (id, df, dt) VALUES (@int_var, @dff, @fd)
end
close some_cursor
deallocate some_cursor

2

Re: Cursors cycles consultation

BSCHECK;
That is, in other words - "drive" of the last nonblank value the subsequent lines is necessary to you? For this purpose would be ideally suited last_value () over (order by... ignore nulls), but in MS it while is not present. But it is possible to replace it analytical MAX th with small  a file...

3

Re: Cursors cycles consultation

Anna wrote:

BSCHECK;
That is, in other words - "drive" of the last nonblank value the subsequent lines is necessary to you? For this purpose would be ideally suited last_value () over (order by... ignore nulls), but in MS it while is not present. But it is possible to replace it analytical MAX th with small  a file...

But is first_value

4

Re: Cursors cycles consultation

TaPaK;
Told - "and", speak - ()
Show, how you will apply it here without ignore nulls.
But even if apply... The further logic is not absolutely clear. That will be considered as the continuous group of lines in which end should be "the last line should contain date 2 as closing" () but it not to you a question, and the HARDWARE...

5

Re: Cursors cycles consultation

Anna, Tarak as recommend?
Or help  the cursor?
MS SQL 2016

6

Re: Cursors cycles consultation

Anna wrote:

BSCHECK;
That is, in other words - "drive" of the last nonblank value the subsequent lines is necessary to you? For this purpose would be ideally suited last_value () over (order by... ignore nulls), but in MS it while is not present. But it is possible to replace it analytical MAX th with small  a file...

All-taki would name more likely  lines, it is visible by an example that 2 line  with 3 and from 2 lines undertakes df, and from 3 - fd

7

Re: Cursors cycles consultation

BSCHECK;
But thus for some reason dates dt from 3 and 5 lines (did not look further) absolutely disappear
Criterion not exact absolutely.

8

Re: Cursors cycles consultation

ShIgor wrote:

BSCHECK;
But thus for some reason dates dt from 3 and 5 lines (did not look further) absolutely disappear
Criterion not exact absolutely.

I do not know how even more precisely to describe. Actually dt it is necessary only for the last line.
We take a line if fd it is filled it is writeable id, df, fd if - is not present we pass by next line if again it is not filled, again to the following. When we meet filled it is writeable id, df - from the first line and fd from that where it is filled. Well for example on these to 7 lines:
select 1 as id, ' 2013-02-22 00:00:00.000 ' as df, ' 2013-03-21 00:00:00.000 ' as dt, ' 2013-03-01 00:00:00.000 ' as fd union
select 1 as id, ' 2013-03-22 00:00:00.000 ' as df, ' 2013-04-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2013-04-22 00:00:00.000 ' as df, ' 2013-05-21 00:00:00.000 ' as dt, ' 2013-04-30 00:00:00.000 ' as fd union all
select 1 as id, ' 2013-06-22 00:00:00.000 ' as df, ' 2013-07-21 00:00:00.000 ' as dt, ' 2013-07-12 00:00:00.000 ' as fd union all
select 1 as id, ' 2013-07-22 00:00:00.000 ' as df, ' 2013-08-21 00:00:00.000 ' as dt, ' 2013-08-14 00:00:00.000 ' as fd union all
select 1 as id, ' 2013-09-22 00:00:00.000 ' as df, ' 2013-10-21 00:00:00.000 ' as dt, ' NULL ' as fd union all
select 1 as id, ' 2013-10-22 00:00:00.000 ' as df, ' 2013-11-21 00:00:00.000 ' as dt, ' 2013-10-28 00:00:00.000 ' as fd
Should turn out a trace. 5 records:
1 2013-02-22 00:00:00.000 2013-03-01 00:00:00.000
1 2013-03-22 00:00:00.000 2013-04-30 00:00:00.000
1 2013-06-22 00:00:00.000 2013-07-12 00:00:00.000
1 2013-07-22 00:00:00.000 2013-08-14 00:00:00.000
1 2013-09-22 00:00:00.000 2013-10-28 00:00:00.000

9

Re: Cursors cycles consultation

BSCHECK wrote:

we take date 3 of the next line and .

Than "the next line" is defined?
All id are equal for some reason 1...
P.S. Forget for a while a word "cursor".

10

Re: Cursors cycles consultation

iap wrote:

it is passed...
Than "the next line" is defined?
All id are equal for some reason 1...
P.S. Forget for a while a word "cursor".

The next line is defined df. c ascending sort order.
For an example resulted id =1 since we consider a line and dates in frames id.
Naturally, such id in the table it is a lot of.

11

Re: Cursors cycles consultation

select
t.id, min (t.df), b.fd
from
[There is a label] t outer apply
(select top (1) fd from [there is a label] where t.fd is null and id = t.id and df> t.df and fd is not null order by df) a cross apply
(select isnull (t.fd, a.fd)) b (fd)
group by
t.id, b.fd
order by
t.id, min (t.df), b.fd;

12

Re: Cursors cycles consultation

invm wrote:

select
t.id, min (t.df), b.fd
from
[There is a label] t outer apply
(select top (1) fd from [there is a label] where t.fd is null and id = t.id and df> t.df and fd is not null order by df) a cross apply
(select isnull (t.fd, a.fd)) b (fd)
group by
t.id, b.fd
order by
t.id, min (t.df), b.fd;

Thanks, INVM! Yes you directly the magician and the magician!

13

Re: Cursors cycles consultation

Sports interest - so works (not up to the end I understand a condition)?

select id, min (df) df, max (fd) fd
from
(
select *, sum (gs) over (partition by id order by df) gr
from
(
select
id, df, fd, iif ((lag (fd) over (partition by id order by df) is not null), 1, 0) gs
from [there is a label]
) t
) t
group by id, gr

14

Re: Cursors cycles consultation

LameUser wrote:

Sports interest - so works (not up to the end I understand a condition)?

select id, min (df) df, max (fd) fd
from
(
select *, sum (gs) over (partition by id order by df) gr
from
(
select
id, df, fd, iif ((lag (fd) over (partition by id order by df) is not null), 1, 0) gs
from [there is a label]
) t
) t
group by id, gr

Yes, so too works. 1 more magician))