Insert a batch number

1

I have a MSSql table that has a columns named TodaysDate, UtilityNo, Branch and Batch. I want to write a SQl that starts out with a batch of 1, and when at the 37th row or every 36th , will increment the batch number to 2, etc. I want to update the Batch Column to this number.
So when each Todaysdate --> Branch --> utilityNo gets to row 37 or 73 ... increment and update the batchno in the row. Sounds simple but I am stuck.

Thanks for any solution.

2

You could do it with a computed column:

CREATE TABLE mytable(...

Batch as (UtilityNo % 36)

...)

3 
UPDATE D
SET Batch = (row_no - 1) / 36 + 1
FROM
(
    SELECT *, row_no = ROW_NUMBER() OVER (PARTITION BY TodaysDate , Branch , utilityNo 
                                              ORDER BY Batch )
    FROM   yourtable
) D