John Mitchell-245523 - Friday, January 20, 2017 9:13 AM

s-sql - Friday, January 20, 2017 8:52 AM

Need help on a query to generate a two incremental values on below table.
Both the BatchNo and BatchSequenceNo should begin at 1.
The BatchSequenceNo should increment by 1, upto 500 rows.
At 501 row, the BatchNo should be incremented by 1,(ie BatchNo = 2) and
the BatchSequenceNo should reset from 1.
BatchNo values needs to incremented by 1, for every 500 rows.


CREATE TABLE #tblBatch
   (BatchId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   Sname VARCHAR(50) NOT NULL,
        BatchNo int,
        BatchSequenceNo int
        )

---Load some sample data
INSERT INTO #tblBatch(Sname)
Select top 2010 'A -SampleText-'+cast(row_number() over(order by t1.number) as varchar) as N
from master..spt_values t1
   cross join master..spt_values t2


UPDATE #tblBatch
SET
    BatchSequenceNo = CASE WHEN BatchId%500 = 0 THEN 500 ELSE BatchId%500 END
,   BatchNo = (BatchId - 1)/500 + 1
John