qvFGZDaB

1How to make this sql query
2Turns-time
3
4cod_turn (PrimaryKey) 
5time     (datetime)
6
7Taken turns
8
9cod_taken_turn (Primary Key)
10cod_turn
11...
12	
138:00 (not taken)
14 9:00 (not taken)
1510:00 (taken)
1611:00 (not taken)
1712:00 (not taken)
1813:00 (not taken)
1914:00 (taken)
20	
2111:00
2212:00
2313:00
24	
25WITH Base AS (
26SELECT *,
27       CASE WHEN EXISTS(
28               SELECT * 
29               FROM Taken_turns taken 
30               WHERE taken.cod_turn = turns.cod_turn) THEN 1 ELSE 0 END AS taken
31FROM   [Turns-time] turns)
32, RecursiveCTE As (
33SELECT TOP 1 cod_turn, [time], taken AS run, 0 AS grp
34FROM  Base 
35WHERE [time] >= @start_time
36ORDER BY [time]
37UNION   ALL
38SELECT  R.cod_turn, R.[time], R.run, R.grp
39FROM    (
40        SELECT  T.*,
41                CASE WHEN T.taken = 0 THEN 0 ELSE run+1 END AS run,
42                CASE WHEN T.taken = 0 THEN grp + 1 ELSE grp END AS grp,
43                rn = ROW_NUMBER() OVER (ORDER BY T.[time])
44        FROM    Base T
45        JOIN    RecursiveCTE R
46                ON  R.[time] < T.[time]
47        ) R
48WHERE   R.rn = 1 AND run < @run_length
49),  T AS(
50SELECT *, 
51        MAX(grp) OVER () AS FinalGroup,
52        COUNT(*) OVER (PARTITION BY grp) AS group_size
53FROM RecursiveCTE
54)
55SELECT cod_turn,time
56FROM T 
57WHERE grp=FinalGroup AND group_size=@run_length
58	
59declare @GivenTime datetime,
60        @GivenSequence int;
61
62select  @GivenTime = cast('08:00' as datetime),
63        @GivenSequence = 3;
64
65declare @sequence int,
66        @code_turn int, 
67        @time datetime,
68        @taked int,
69        @firstTimeInSequence datetime;
70
71set @sequence = 0;
72
73declare turnCursor cursor FAST_FORWARD for 
74                        select turn.cod_turn, turn.[time], taken.cod_taken_turn
75                        from [Turns-time] as turn
76                        left join [Taken turns] as taken on turn.cod_turn = taken.cod_turn
77                        where turn.[time]  >= @GivenTime
78                        order by turn.[time] asc;
79
80open turnCursor;
81fetch next from turnCursor into @code_turn, @time, @taked;
82
83while @@fetch_status = 0 AND @sequence < @GivenSequence
84begin
85    if @taked IS NULL
86        select  @firstTimeInSequence = coalesce(@firstTimeInSequence, @time)
87                ,@sequence = @sequence + 1;
88    else
89        select  @sequence = 0,
90                @firstTimeInSequence = null;
91
92    fetch next from turnCursor into @code_turn, @time, @taked;
93end
94
95close turnCursor;
96deallocate turnCursor;
97
98if @sequence = @GivenSequence
99    select top (@GivenSequence) * from [Turns-time] where [time] >= @firstTimeInSequence
100    order by [time] asc
101	
102CREATE TABLE #CONSECUTIVE_TURNS (id int identity, time datetime, consecutive int)
103
104INSERT INTO #CONSECUTIVE_TURNS (time, consecutive, 0)
105      SELECT cod_turn
106           , time
107           , 0
108        FROM Turns-time
109    ORDER BY time
110
111DECLARE @i int
112        @n int
113SET @i = 0
114SET @n = 3  -- Number of consecutive not taken records
115
116while (@i < @n) begin
117    UPDATE #CONSECUTIVE_TURNS
118       SET consecutive = consecutive + 1
119     WHERE not exists (SELECT 1
120                         FROM Taken-turns
121                        WHERE id = cod_turn + @i
122                      )
123
124    SET @i = @i + 1
125end
126
127DECLARE @firstElement int
128
129SELECT @firstElement = min(id)
130  FROM #CONSECUTIVE_TURNS
131 WHERE consecutive >= @n
132
133SELECT *
134  FROM #CONSECUTIVE_TURNS
135 WHERE id between @firstElement 
136              and @firstElement + @n - 1
137	
138SELECT TOP 3 time FROM [turns-time] WHERE time >= (
139
140  -- get first result of the 3 consecutive results
141  SELECT TOP 1 time AS first_result
142  FROM [turns-time] tt
143
144  -- start from given time, which is 8:00 in this case
145  WHERE time >= '08:00'
146
147  -- turn is not taken
148  AND cod_turn NOT IN (SELECT cod_turn FROM taken_turns)
149
150  -- 3 consecutive turns from current turn are not taken
151  AND (
152    SELECT COUNT(*) FROM 
153      (
154        SELECT TOP 3 cod_turn AS selected_turn FROM [turns-time] tt2 WHERE tt2.time >= tt.time
155        GROUP BY cod_turn ORDER BY tt2.time
156      ) AS temp
157    WHERE selected_turn NOT IN (SELECT cod_turn FROM taken_turns)) = 3
158) ORDER BY time
159	
160DECLARE @Results TABLE
161(
162     cod_turn   INT NOT NULL
163    ,[status]   TINYINT NOT NULL
164    ,RowNumber  INT PRIMARY KEY
165);
166INSERT  @Results (cod_turn, [status], RowNumber)
167SELECT   a.cod_turn
168        ,CASE WHEN b.cod_turn IS NULL THEN 1 ELSE 0 END [status] --1=(not taken), 0=(taken)
169        ,ROW_NUMBER() OVER(ORDER BY a.[time]) AS RowNumber
170FROM    [Turns-time] a
171LEFT JOIN [Taken_turns] b ON a.cod_turn = b.cod_turn 
172WHERE   a.[time] >= @Start;
173
174--SELECT * FROM @Results r ORDER BY r.RowNumber;
175
176SELECT  *
177FROM
178(
179SELECT  TOP(1) ca.LastRowNumber
180FROM    @Results a
181CROSS APPLY 
182(
183    SELECT SUM(c.status) CountNotTaken, MAX(c.RowNumber) LastRowNumber
184    FROM
185    (
186        SELECT  TOP(@Len)
187                b.RowNumber, b.[status]
188        FROM    @Results b
189        WHERE   b.RowNumber <= a.RowNumber 
190        ORDER BY b.RowNumber DESC
191    ) c
192) ca
193WHERE   ca.CountNotTaken = @Len
194ORDER BY a.RowNumber ASC
195) x INNER JOIN @Results y ON x.LastRowNumber - @Len + 1 <= y.RowNumber AND y.RowNumber <= x.LastRowNumber;