How to make this sql query

Question

I have 2 SQL Server tables with the following structure

Turns-time

cod_turn (PrimaryKey) 
time     (datetime)

Taken turns

cod_taken_turn (Primary Key)
cod_turn
...

and several other fields which are irrelevant to the problem. I cant alter the table structures because the app was made by someone else.

given a numeric variable parameter, which we will assume to be "3" for this example, and a given time, I need to create a query which looking from that time on, it looks the first 3 consecutive records by time which are not marked as "taken". For example:

For example, for these turns, starting by the time of "8:00" chosen by the user

 8:00 (not taken)
 9:00 (not taken)
10:00 (taken)
11:00 (not taken)
12:00 (not taken)
13:00 (not taken)
14:00 (taken)

The query it would have to list

11:00
12:00
13:00

I cant figure out how to make the query in pure sql, if possible.

Answer 1

Another set-based solution (tested):

DECLARE @Results TABLE
(
     cod_turn   INT NOT NULL
    ,[status]   TINYINT NOT NULL
    ,RowNumber  INT PRIMARY KEY
);
INSERT  @Results (cod_turn, [status], RowNumber)
SELECT   a.cod_turn
        ,CASE WHEN b.cod_turn IS NULL THEN 1 ELSE 0 END [status] --1=(not taken), 0=(taken)
        ,ROW_NUMBER() OVER(ORDER BY a.[time]) AS RowNumber
FROM    [Turns-time] a
LEFT JOIN [Taken_turns] b ON a.cod_turn = b.cod_turn 
WHERE   a.[time] >= @Start;

--SELECT * FROM @Results r ORDER BY r.RowNumber;

SELECT  *
FROM
(
SELECT  TOP(1) ca.LastRowNumber
FROM    @Results a
CROSS APPLY 
(
    SELECT SUM(c.status) CountNotTaken, MAX(c.RowNumber) LastRowNumber
    FROM
    (
        SELECT  TOP(@Len)
                b.RowNumber, b.[status]
        FROM    @Results b
        WHERE   b.RowNumber <= a.RowNumber 
        ORDER BY b.RowNumber DESC
    ) c
) ca
WHERE   ca.CountNotTaken = @Len
ORDER BY a.RowNumber ASC
) x INNER JOIN @Results y ON x.LastRowNumber - @Len + 1 <= y.RowNumber AND y.RowNumber <= x.LastRowNumber;

Answer 2

Pure SQL

SELECT TOP 3 time FROM [turns-time] WHERE time >= (

  -- get first result of the 3 consecutive results
  SELECT TOP 1 time AS first_result
  FROM [turns-time] tt

  -- start from given time, which is 8:00 in this case
  WHERE time >= '08:00'

  -- turn is not taken
  AND cod_turn NOT IN (SELECT cod_turn FROM taken_turns)

  -- 3 consecutive turns from current turn are not taken
  AND (
    SELECT COUNT(*) FROM 
      (
        SELECT TOP 3 cod_turn AS selected_turn FROM [turns-time] tt2 WHERE tt2.time >= tt.time
        GROUP BY cod_turn ORDER BY tt2.time
      ) AS temp
    WHERE selected_turn NOT IN (SELECT cod_turn FROM taken_turns)) = 3
) ORDER BY time

Note: I tested it on Postgresql (with some code modification), but not MS SQL Server. I'm not sure about performance compared to T-SQL.

Answer 3

WITH Base AS (
SELECT *,
       CASE WHEN EXISTS(
               SELECT * 
               FROM Taken_turns taken 
               WHERE taken.cod_turn = turns.cod_turn) THEN 1 ELSE 0 END AS taken
FROM   [Turns-time] turns)
, RecursiveCTE As (
SELECT TOP 1 cod_turn, [time], taken AS run, 0 AS grp
FROM  Base 
WHERE [time] >= @start_time
ORDER BY [time]
UNION   ALL
SELECT  R.cod_turn, R.[time], R.run, R.grp
FROM    (
        SELECT  T.*,
                CASE WHEN T.taken = 0 THEN 0 ELSE run+1 END AS run,
                CASE WHEN T.taken = 0 THEN grp + 1 ELSE grp END AS grp,
                rn = ROW_NUMBER() OVER (ORDER BY T.[time])
        FROM    Base T
        JOIN    RecursiveCTE R
                ON  R.[time] < T.[time]
        ) R
WHERE   R.rn = 1 AND run < @run_length
),  T AS(
SELECT *, 
        MAX(grp) OVER () AS FinalGroup,
        COUNT(*) OVER (PARTITION BY grp) AS group_size
FROM RecursiveCTE
)
SELECT cod_turn,time
FROM T 
WHERE grp=FinalGroup AND group_size=@run_length

Answer 4

with a cursor

declare @GivenTime datetime,
        @GivenSequence int;

select  @GivenTime = cast('08:00' as datetime),
        @GivenSequence = 3;

declare @sequence int,
        @code_turn int, 
        @time datetime,
        @taked int,
        @firstTimeInSequence datetime;

set @sequence = 0;

declare turnCursor cursor FAST_FORWARD for 
                        select turn.cod_turn, turn.[time], taken.cod_taken_turn
                        from [Turns-time] as turn
                        left join [Taken turns] as taken on turn.cod_turn = taken.cod_turn
                        where turn.[time]  >= @GivenTime
                        order by turn.[time] asc;

open turnCursor;
fetch next from turnCursor into @code_turn, @time, @taked;

while @@fetch_status = 0 AND @sequence < @GivenSequence
begin
    if @taked IS NULL
        select  @firstTimeInSequence = coalesce(@firstTimeInSequence, @time)
                ,@sequence = @sequence + 1;
    else
        select  @sequence = 0,
                @firstTimeInSequence = null;

    fetch next from turnCursor into @code_turn, @time, @taked;
end

close turnCursor;
deallocate turnCursor;

if @sequence = @GivenSequence
    select top (@GivenSequence) * from [Turns-time] where [time] >= @firstTimeInSequence
    order by [time] asc

Answer 5

I think there is not a simple way to achieve this.

But probably there are many complex ways :). This is an approach that should work in Transact-SQL:

CREATE TABLE #CONSECUTIVE_TURNS (id int identity, time datetime, consecutive int)

INSERT INTO #CONSECUTIVE_TURNS (time, consecutive, 0)
      SELECT cod_turn
           , time
           , 0
        FROM Turns-time
    ORDER BY time

DECLARE @i int
        @n int
SET @i = 0
SET @n = 3  -- Number of consecutive not taken records

while (@i < @n) begin
    UPDATE #CONSECUTIVE_TURNS
       SET consecutive = consecutive + 1
     WHERE not exists (SELECT 1
                         FROM Taken-turns
                        WHERE id = cod_turn + @i
                      )

    SET @i = @i + 1
end

DECLARE @firstElement int

SELECT @firstElement = min(id)
  FROM #CONSECUTIVE_TURNS
 WHERE consecutive >= @n

SELECT *
  FROM #CONSECUTIVE_TURNS
 WHERE id between @firstElement 
              and @firstElement + @n - 1

This is untested but I think it will work.