January 20, 2017 at 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
January 20, 2017 at 9:13 am
s-sql - Friday, January 20, 2017 8:52 AMNeed 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
January 20, 2017 at 9:16 am
s-sql - Friday, January 20, 2017 8:52 AMNeed 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
;WITH Majors AS (SELECT BatchId = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values)
SELECT TOP(2010) *
FROM Majors
CROSS APPLY (SELECT TOP(200) rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) x
ORDER BY BatchId, rn
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 20, 2017 at 9:32 am
John Mitchell-245523 - Friday, January 20, 2017 9:13 AMs-sql - Friday, January 20, 2017 8:52 AMNeed 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
This solution worked perfectly..!! Thanks a Ton.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply