CODEWITHSUNDEEP
sql
Raj P
Raj P

Reputation: 29

Update the Column values as grouped Sequence number based on input number provided in sql server

I want to update batch column here

   ID   CustId  Batch
    1   100     NULL
    2   101     NULL
    3   102     NULL
    4   103     NULL
    5   104     NULL
    6   105     NULL
    7   106     NULL
    8   107     NULL
    9   108     NULL
    10  109     NULL
    11  110     NULL

Based on the input number provided, lets say I have input 2. Logic here is Divide the Number of records by input number. In this case it becomes 11/2 =5.5

The result should look like below

  ID    CustId  Batch
    1   100     1
    2   101     1
    3   102     2
    4   103     2
    5   104     3
    6   105     3
    7   106     4
    8   107     4
    9   108     5
    10  109     5
    11  110     6

Please suggest an sql query for this.

Upvotes: 1

Views: 267

Answers (2)

Hemant Halwai
Hemant Halwai

Reputation: 436

The below code will generate the required result. The reason for taking @input as DECIMAL is, it will consider the decimal values and not just round of to the INT value.

 DECLARE @input DECIMAL = 2;
    WITH CTE AS (
      SELECT t.ID, ROW_NUMBER() OVER (ORDER BY ID) AS rowNo
      FROM Table t
    )
    UPDATE t
        SET Batch = CEILING( c.rowNo / @input)
        FROM Table t
        INNER JOIN CTE c
          ON t.ID = c.ID
        

To check the output before running Update

    -- DECLARE @input DECIMAL = 2
    -- SELECt t.*, CEILING( ROW_NUMBER() OVER (ORDER BY ID) / @input) as NewBatch
    --      FROM
    --      Table t

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

One method is:

select t.*,
       ntile(ceiling(cnt / @n)) over (order by id) as batch
from (select t.*, count(*) over () as cnt
      from t
     ) t;

Another method is:

select t.*,
       floor( (row_number() over (order by id) - 1) / n) as batch
from t;

Upvotes: 1

Related Questions