Give number to rows based on condition

all. I am trying to create another column called 'sessionid' for the following data; When the newsession row = 'YES'. I want the next group of rows value for 'sessionid' to be incremented by 1 until we find another 'YES'. So for example:

I am wondering if there is any way to condition the rank function, but not sure how to do it; Any help will be really appreciated!

In a subquery, put a 1 in a new column every time the previous record is Yes:

SELECT 
    CASE WHEN LAG(newsession) OVER (PARTITION BY empid ORDER BY date_Time ASC) = 'Yes' THEN 1 ELSE 0 END as newsession_found,
    yourtable.*
FROM yourtable

Then in an outer query using this subquery's result set, run a cumulative sum on this new column:

SELECT 
   newsession,
   empid,
   date_time
   SUM(newsession_found) OVER (PARTITION BY empid ORDER BY date_Time ASC ROWS UNBOUNDED PRECEDING) AS sessionid
FROM 
  (
      SELECT 
         CASE WHEN LAG(newsession) OVER (PARTITION BY empid ORDER BY date_Time ASC) = 'Yes' THEN 1 ELSE 0 END as newsession_found,
         yourtable.*
      FROM yourtable
  ) sub;

I didn't managed to get the results you're looking for in a single query, but if you can iterate over the records, then your problem becomes much simpler.

You'd have to test the performance of a query like this with a much larger data set to see how it holds up.

------------------------------------------------------------------------------------------------------------------
--  build sample data
------------------------------------------------------------------------------------------------------------------
    declare @sessiontable table(
            newsession varchar(55),
            empid int,
            date_time datetime,
            sessionId int)

insert into @sessiontable(newsession, empid, date_time)
values
('NO','12345','2023-01-18 00:01:40'),
('NO','12345','2023-01-18 00:01:46'),
('NO','12345','2023-01-18 00:01:42'),
('YES','12345','2023-01-18 00:01:46'),
('NO','12345','2023-01-18 01:34:48'),
('NO','12345','2023-01-18 02:01:59'),
('NO','12345','2023-01-18 02:02:06'),
('YES','12345','2023-01-18 02:02:12'),
('NO','12345','2023-01-18 12:50:45'),
('NO','12345','2023-01-18 12:50:45')

------------------------------------------------------------------------------------------------------------------
--  id each record
------------------------------------------------------------------------------------------------------------------
       drop table if exists #numberedRows
     select *, row_number() over (order by date_time) as row_number
       into #numberedRows
       from @sessiontable

------------------------------------------------------------------------------------------------------------------
--  iterate over the records
------------------------------------------------------------------------------------------------------------------
    declare @currentRowNumber int = 1
    declare @currentSessionId int = 1
      while exists(select 1 from #numberedRows where sessionId is null)
      begin
            declare @newssion varchar(55)
             select @newssion = isnull(newsession, 'NO') from #numberedRows where row_number = (@currentRowNumber - 1)
                 if (@newssion = 'YES')
              begin
                    set @currentSessionId = @currentSessionId + 1
                end

             update #numberedRows
                set sessionId = @currentSessionId
              where row_number = @currentRowNumber

                set @currentRowNumber = @currentRowNumber + 1
        end

     select newsession,
            empid,
            date_time,
            sessionId
       from #numberedRows

Results: