Transact-SQL - the number of rows until the condition is met

Question

I am trying to generate the numbers in the x column, given the values ​​in the eq field, so that it must assign a number for each record until it matches the value 1, and the next line should reset and start counting again. I tried with row_number, but the problem is that in the column that I need to evaluate, I have only those and zeros, and the cases that I saw with row_number used growing values ​​in the column. Also tried with rank, but I failed to get it to work.

nInd Fecha Tipo @Inicio @contador_I @Final @contador_F eq x 1 18/03/2002 I 18/03/2002 1 null null 0 1 2 20/07/2002 F 18/03/2002 1 20/07/2002 1 1 2 3 19/08/2002 I 19/08/2002 2 20/07/2002 1 0 1 4 21/12/2002 F 19/08/2002 2 21/12/2002 2 1 2 5 17/03/2003 I 17/03/2003 3 21/12/2002 2 0 1 6 01/04/2003 I 17/03/2003 4 21/12/2002 2 0 2 7 07/04/2003 I 17/03/2003 5 21/12/2002 2 0 3 8 02/06/2003 F 17/03/2003 5 02/06/2003 3 0 4 9 31/07/2003 F 17/03/2003 5 31/07/2003 4 0 5 10 31/08/2003 F 17/03/2003 5 31/08/2003 5 1 6 11 01/09/2005 I 01/09/2005 6 31/08/2003 5 0 1 12 05/09/2005 I 01/09/2005 7 31/08/2003 5 0 2 13 31/12/2005 F 01/09/2005 7 31/12/2005 6 0 3 14 14/01/2006 F 01/09/2005 7 14/01/2006 7 1 4 

Answer 1

Another solution available:

 select nind, eq, row_number() over (partition by s order by s) from ( select nind, eq, coalesce(( select sum(eq) +1 from mytable pre where pre.nInd < mytable.nInd) ,1) s --this is the sum of eq! from mytable) g 

An internal subquery creates groups sequentially for each occurrence of 1 in eq . Then we can use row_number() over partition to get our counter.

Below is an example using Sql Server

Answer 2

I have two answers here. One of them is based on ROW_NUMBER() , and the other is based on what seems to be your index ( nInd ). I was not sure if there would be a gap in your index, so I did ROW_NUMBER() .

My table format was as follows:

myIndex int identity(1,1) NOT NULL number int NOT NULL

First ROW_NUMBER() ...

 WITH rn AS (SELECT *, ROW_NUMBER() OVER (ORDER BY myIndex) AS rn, COUNT(*) AS max FROM counting c GROUP BY c.myIndex, c.number) ,cte (myIndex, number, level, row) AS ( SELECT r.myIndex, r.number, 1, r.rn + 1 FROM rn r WHERE r.rn = 1 UNION ALL SELECT r1.myIndex, r1.number, CASE WHEN r1.number = 0 AND r2.number = 1 THEN 1 ELSE c.level + 1 END, row + 1 FROM cte c JOIN rn r1 ON c.row = r1.rn JOIN rn r2 ON c.row - 1 = r2.rn ) SELECT c.myIndex, c.number, c.level FROM cte c OPTION (MAXRECURSION 0); 

Now the index ...

 WITH cte (myIndex, number, level) AS ( SELECT c.myIndex + 1, c.number, 1 FROM counting c WHERE c.myIndex = 1 UNION ALL SELECT c1.myIndex + 1, c1.number, CASE WHEN c1.number = 0 AND c2.number = 1 THEN 1 ELSE c.level + 1 END FROM cte c JOIN counting c1 ON c.myIndex = c1.myIndex JOIN counting c2 ON c.myIndex - 1 = c2.myIndex ) SELECT c.myIndex - 1 AS myIndex, c.number, c.level FROM cte c OPTION (MAXRECURSION 0); 

Answer 3

The answer I have is to use

 Cursor 

I know if there is another solution without a cursor, it will be better for performance aspects

Here is a brief description of my solution:

  -- Create DBTest use master Go Create Database DBTest Go use DBTest GO -- Create table Create table Tabletest (nInd int , eq int) Go -- insert dummy data insert into Tabletest (nInd,eq) values (1,0), (2,1), (3,0), (4,1), (5,0), (6,0), (7,0), (8,0), (9,1), (8,0), (9,1) Create table #Tabletest (nInd int ,eq int ,x int ) go DECLARE @nInd int , @eq int , @x int set @x = 1 DECLARE db_cursor CURSOR FOR SELECT nInd , eq FROM Tabletest order by nInd OPEN db_cursor FETCH NEXT FROM db_cursor INTO @nInd , @eq WHILE @@FETCH_STATUS = 0 BEGIN if (@eq = 0) begin insert into #Tabletest (nInd ,eq ,x) values (@nInd , @eq , @x) set @x = @x +1 end else if (@eq = 1) begin insert into #Tabletest (nInd ,eq ,x) values (@nInd , @eq , @x) set @x = 1 end FETCH NEXT FROM db_cursor INTO @nInd , @eq END CLOSE db_cursor DEALLOCATE db_cursor select * from #Tabletest 

The end result will be as follows:

Hope this helps.

Answer 4

Looking at it a little differently (this may be wrong, but eliminates the need for recursive CTE cursors), it looks like you are creating ordered groups in your dataset. So, start by looking for these groups, then determine the order of each of them.

The real key is to define the rules for finding corrective groupings. Based on your description and comments, I assume that the grouping is from the very beginning (ordered by the nInd column) ending on each line, and eq is 1 , so you can do something like:

 ;with ends(nInd, ord) as ( --Find the ending row for each set SELECT nInd, row_number() over(order by nInd) FROM mytable WHERE eq=1 ), ranges(sInd, eInd) as ( --Find the previous ending row for each ending row, forming a range for the group SELECT coalesce(s.nInd,0), e.nInd FROM ends s right join ends e on s.ord=e.ord-1 ) 

Then, using these ranges of groups, you can find the final order of each of them:

 select t.nInd, t.Fecha, t.eq ,[x] = row_number() over(partition by sInd order by nInd) from ranges r join mytable t on r.sInd < t.nInd and t.nInd <= r.eInd order by t.nInd