How to check consecutive values in rows in TSQL?

Question

I have the following table:

And I am looking for an output like this with a consecutive available two slots. Preferably the consecutive number should be a variable like @n = 2.

How to achieve this in TSQL? Thanks!

Update 8/3/2022:

I undeleted this question as it is not fair to @lptr, as he/she has the correct solution, but didn't put that in answer section.

Sean Lange: Sorry, my question was not very clear for the first time, but basically the query is looking at the SLOT column with Available=Y data, and need to output the first occurrence of the row where SLOT numbers are "N" consecutive numbers, in this case 2.

Yes, order by user and then slot. It is also acceptable if the sort order is by slot and user. — WinFXGuy, Aug 01 '22 at 18:37
Can you explain the output? I have looked at this several times and it is not at all clear to me why those numbers are expected as output. — Sean Lange, Aug 01 '22 at 18:43
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question, no images. — Yitzhak Khabinsky, Aug 01 '22 at 18:49
@lptr You got it, exactly this is what I am looking for. Please move your comment to answers, I will mark it as a correct solution. Thanks! — WinFXGuy, Aug 03 '22 at 19:05
This class of problem is, FYI, called [tag:gaps-and-islands]. — O. Jones, Aug 03 '22 at 19:12

score 1 · Accepted Answer · answered Aug 03 '22 at 19:30

Posting this on behalf of @lptr:

create table t(usr varchar(10), slot int, avl char(1));

insert into t(usr, slot, avl)
values
('bob', 1, 'y'),
('bob', 2, 'n'),
('bob', 3, 'n'),
('bob', 4, 'y'),
('bob', 5, 'y'),
('bob', 6, 'y'),
('bob', 7, 'y'),
('bob', 8, 'y'),
('bob', 9, 'n'),
('bob', 10, 'y'),
('bob', 11, 'y'),
('bob', 12, 'y');


select *
from
(
 select *, count(*) over(partition by usr, grp) as grpYmembercount, row_number() over(partition by usr, grp order by slot) as grprn
 from
 (
  select *, sum(case when avl = 'n' then 1 else 0 end) over(partition by usr order by slot rows unbounded preceding) as grp
  from t
 ) as g
 where avl = 'y'
) as c
where c.grpYmembercount - grprn + 1 >= 2;

How to check consecutive values in rows in TSQL?

1 Answers1