SQL Server : compare dates between rows

Question

I'm working with SQL server 2016 and I can't figure out how to build that query.

Let's say I have a table like this:

ID  EntryTime               ResultTime
1   2016-05-02 13:30:00     2016-05-02 21:50:00
2   2016-05-02 14:45:00     2016-05-02 22:00:00
3   2016-05-02 16:30:00     2016-05-02 22:21:00
4   2016-05-03 01:00:00     2016-05-03 03:33:00
5   2016-05-03 10:30:00     2016-05-04 07:47:00
6   2016-05-03 12:30:00     2016-05-03 22:45:00
7   2016-05-04 11:30:00     2016-05-05 21:30:00
8   2016-05-04 12:30:00     2016-05-04 22:58:00
9   2016-05-04 13:30:00     2016-05-04 23:04:00
10  2016-05-04 13:45:00     2016-05-04 22:59:00
11  2016-05-04 14:00:00     2016-05-04 22:59:00
12  2016-05-04 14:15:00     2016-05-04 23:04:00
13  2016-05-04 17:45:00     2016-05-04 21:47:00
14  2016-05-05 23:30:00     2016-05-06 03:25:00
15  2016-05-05 23:45:00     2016-05-06 03:30:00
16  2016-05-06 00:00:00     2016-05-06 03:32:00
17  2016-05-06 00:15:00     2016-05-06 03:31:00
18  2016-05-06 00:30:00     2016-05-06 03:25:00
19  2016-05-06 00:45:00     2016-05-06 02:50:00
20  2016-05-06 01:00:00     2016-05-06 03:25:00

I want to select just the rows that the entry datetime is after the last chosen result datetime.

For example: the result time for line 1 is "2016-05-02 21:50:00" so the next row will be line 4 because this is the first line that the entry time is after the result time that was last selected, the next line suppose to be after the result time of line 4 (After "2016-05-03 03:33:00") so the next line will be line 5.

The requested result is:

ID  EntryTime               ResultTime
1   2016-05-02 13:30:00     2016-05-02 21:50:00
4   2016-05-03 01:00:00     2016-05-03 03:33:00
5   2016-05-03 10:30:00     2016-05-04 07:47:00
7   2016-05-04 11:30:00     2016-05-05 21:30:00
14  2016-05-05 23:30:00     2016-05-06 03:25:00

Answer 1

I think sometimes cursor is the answer

CREATE FUNCTION getSelected
(    
)
RETURNS @res TABLE 
(
    id int, EntryTime DATETIME, ResultTime DATETIME
)
AS
BEGIN
    declare @idC int;
    declare @ResultTimeC DATETIME;
    declare @EntryTimeC DATETIME;
    declare @lastNextDate DATETIME;

    DECLARE Iterator CURSOR LOCAL FAST_FORWARD 
    FOR SELECT id, EntryTime, ResultTime FROM dbo.tt1 order by EntryTime, id
    OPEN Iterator
    WHILE 1=1 BEGIN
        FETCH NEXT FROM Iterator INTO @idC, @EntryTimeC, @ResultTimeC
        IF @@FETCH_STATUS < 0 BREAK 

        if(@lastNextDate is null or @lastNextDate < @EntryTimeC) begin
            set @lastNextDate = @ResultTimeC;
            insert into @res (id, EntryTime, ResultTime) values (@idC, @EntryTimeC, @ResultTimeC);
        end;            
    END
    CLOSE Iterator
    DEALLOCATE Iterator;

    RETURN 
END

EDIT

And the multiple symbol version

CREATE FUNCTION getSelected2
(    
)
RETURNS @res TABLE 
(
    id int, EntryTime DATETIME, ResultTime DATETIME, Symbol char(3)
)
AS
BEGIN
    declare @idC int;
    declare @ResultTimeC DATETIME;
    declare @EntryTimeC DATETIME;
    declare @SymbolC char(3);
    declare @lastNextDate DATETIME; 
    declare @lastSymbol char(3);

    DECLARE Iterator CURSOR FAST_FORWARD 
    FOR SELECT id, EntryTime, ResultTime, Symbol FROM dbo.tt2 order by Symbol, EntryTime, id
    OPEN Iterator
    WHILE 1=1 BEGIN
        FETCH NEXT FROM Iterator INTO @idC, @EntryTimeC, @ResultTimeC, @SymbolC
        IF @@FETCH_STATUS < 0 BREAK 

        if(@lastSymbol is null or @lastSymbol <> @SymbolC) begin
            set @lastSymbol = @SymbolC;
            set @lastNextDate = null;
        end;
        if(@lastNextDate is null or @lastNextDate < @EntryTimeC) begin
            set @lastNextDate = @ResultTimeC;
            insert into @res (id, EntryTime, ResultTime, Symbol) values (@idC, @EntryTimeC, @ResultTimeC, @SymbolC);
        end;            
    END
    CLOSE Iterator
    DEALLOCATE Iterator;

    RETURN 
END

Answer 2

One way you could do this is by using a recursive CTE to get the next row. For example,

with cte as (
    select *
    from myTable
    where id = 1
    union all
    select t.*
    from myTable t
    cross join cte
    where t.id = (
        select id 
        from (
            select id, row_number() over (order by id) rn 
            from myTable 
            where entrytime > cte.resulttime) z 
        where rn = 1)
    )
select * from cte;

EDIT: For multiple "symbols", here's a method that would work (with example data).

DECLARE @myTable TABLE (Symbol CHAR(3), EntryTime DATETIME, ResultTime DATETIME)
INSERT @myTable VALUES ('AAA','2016-05-02 13:30:00','2016-05-02 21:50:00')
,('AAA','2016-05-02 14:45:00','2016-05-02 22:00:00')
,('AAA','2016-05-02 16:30:00','2016-05-02 22:21:00')
,('AAA','2016-05-03 01:00:00','2016-05-03 03:33:00')
,('AAA','2016-05-03 10:30:00','2016-05-04 07:47:00')
,('AAA','2016-05-03 12:30:00','2016-05-03 22:45:00')
,('AAA','2016-05-04 11:30:00','2016-05-05 21:30:00')
,('AAA','2016-05-04 12:30:00','2016-05-04 22:58:00')
,('AAA','2016-05-04 13:30:00','2016-05-04 23:04:00')
,('AAA','2016-05-04 13:45:00','2016-05-04 22:59:00')
,('AAA','2016-05-04 14:00:00','2016-05-04 22:59:00')
,('AAA','2016-05-04 14:15:00','2016-05-04 23:04:00')
,('AAA','2016-05-04 17:45:00','2016-05-04 21:47:00')
,('AAA','2016-05-05 23:30:00','2016-05-06 03:25:00')
,('AAA','2016-05-05 23:45:00','2016-05-06 03:30:00')
,('AAA','2016-05-06 00:00:00','2016-05-06 03:32:00')
,('AAA','2016-05-06 00:15:00','2016-05-06 03:31:00')
,('AAA','2016-05-06 00:30:00','2016-05-06 03:25:00')
,('AAA','2016-05-06 00:45:00','2016-05-06 02:50:00')
,('AAA','2016-05-06 01:00:00','2016-05-06 03:25:00')
,('BBB','2016-05-02 01:00:00','2016-05-02 03:01:00')
,('BBB','2016-05-02 02:00:00','2016-05-02 03:05:00')
,('BBB','2016-05-02 03:00:00','2016-05-02 03:40:00')
,('BBB','2016-05-02 04:00:00','2016-05-02 04:01:00')
,('BBB','2016-05-02 05:00:00','2016-05-03 07:00:00')
,('BBB','2016-05-02 06:00:00','2016-05-02 07:00:00')
,('BBB','2016-05-03 06:00:00','2016-05-03 07:05:00')
,('BBB','2016-05-04 06:01:00','2016-05-04 07:08:00')
,('BBB','2016-05-04 06:07:00','2016-05-04 07:52:00')
,('BBB','2016-05-05 06:00:00','2016-05-05 07:49:00')
,('CCC','2016-05-05 06:00:00','2016-05-05 07:04:00')
,('CCC','2016-05-05 06:05:00','2016-05-05 06:55:00')
,('CCC','2016-05-05 07:00:00','2016-05-05 07:10:00')
,('CCC','2016-05-05 07:06:00','2016-05-05 08:05:00')
,('CCC','2016-05-05 08:00:00','2016-05-05 08:15:00')
,('CCC','2016-05-05 08:09:00','2016-05-05 09:00:00');

WITH myTable AS (
    SELECT Symbol, EntryTime, ResultTime, ROW_NUMBER() OVER (PARTITION BY Symbol ORDER BY EntryTime) RN
    FROM @myTable)
, CTE AS (
    SELECT *
    FROM myTable
    WHERE RN = 1
    UNION ALL
    SELECT T.*
    FROM CTE
    CROSS APPLY (
        SELECT Symbol, EntryTime, ResultTime, RN
        FROM (
            SELECT *, ROW_NUMBER() OVER (ORDER BY EntryTime) RN2
            FROM myTable
            WHERE Symbol = CTE.Symbol
            AND EntryTime > CTE.ResultTime) Z
        WHERE RN2 = 1) T
    )
SELECT Symbol, EntryTime, ResultTime--, RN [ID?]
FROM CTE
ORDER BY Symbol;

Answer 3

You can use this query:

select *
from @t t1
where not exists (
    select 1
    from @t t2
    where t2.id < t1.id and t2.resultDate > t1.entryDate
)