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.
Advertisement
Answer
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;