Assign Custom Sequence with T-SQL

Add number 20 between different group and continue the sequence.

Create TABLE test(SNo int identity(1,1),StudentID INT,RollNumber int
, Course CHAR(1))

INSERT INTO test (StudentID,Course)
VALUES (123,’A’),(124,’A’),(125,’A’)
,(126,’B’)
,(127,’B’),(128,’B’)
,(129,’C’)
,(130,’C’),(131,’C’)
,(132,’D’)
,(133,’D’),(134,’D’)

;with mycte as (
Select SNo,Course,
dense_rank() OVER(ORDER BY Course) dnk,
ROW_NUMBER() OVER(Partition by Course ORDER BY SNO) rn
,10*Ceiling(count(*)Over(Partition by Course)/10.) cnt
from test
)
,mycte2 as (
select * ,lag(cnt) Over(order by sno) cntlag
from mycte)

select SNo, Course
, Case when dnk=1 then rn else
sum(case when rn =1 then cntlag else 0 end)Over(Order by SNo)+rn +20*(dnk-1) -1 end RollNumber

from mycte2

–Or this

;with mycte as (
Select SNo,Course,
dense_rank() OVER(ORDER BY Course) dnk,
ROW_NUMBER() OVER(Partition by Course ORDER BY SNO) rn
,10*Ceiling(count(*)Over(Partition by Course)/10.) cnt
from test)

select SNo,Course,
case when dnk=1 then rn else
rn+20*(dnk-1)+Sum(Case when rn=1 then ISNULL(cnt2,0) else 0 end) Over(Order by sno) -1 end cnt22
from mycte m
outer apply( select top 1 cnt cnt2 from mycte where dnk<m.dnk order by dnk desc) d

order by sno

drop table test

https://forums.asp.net/t/2146815.aspx?SQL+Server+Query+for+Partitioning+Data

Advertisements