Transact-SQL - number rows until condition met

Question

I'm trying to generate the numbers in the "x" column considering the values in field "eq", in a way that it should assign a number for every record until it meets the value "1", and the next row should reset and start counting again. I've tried with row_number, but the problem is that I only have ones and zeros in the column I need to evaluate, and the cases I've seen using row_number were using growing values in a column. Also tried with rank, but I haven't managed to make it 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

There is 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

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

Here is an example using Sql Server

Answer 2

I have two answers here. One is based off of the ROW_NUMBER() and the other is based off of what appears to be your index (nInd). I wasn't sure if there would be a gap in your index so I made the ROW_NUMBER() as well.

My table format was as follows -

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

First one is 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 that I have now is via using

Cursor

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

here is a quick demo 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 set will be as following:

Hope it helps.

Answer 4

Looking at this a slightly different way (which might not be true, but eliminates the need for cursors of recursive CTEs), it looks like you building ordered groups within your dataset. So, start by finding those groups, then determining the ordering of each of them.

The real key is to determine the rules to find the correcting grouping. Based on your description and comments, I'm guessing the grouping is from the start (ordered by the nInd column) ending at each row with and eq value of 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 group ranges, you can find the final ordering of each:

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