CODEWITHSUNDEEP
sql-server
theo
theo

Reputation: 299

SQL Server Conditional Sequence Number

I have a list of customers from a table and I want to assign each a Sequential number and save it to another table. However, the sequence should restart every month.

Currently, I have this query:

INSERT INTO tblSerialNo
SELECT CustomerNo
      ,FORMAT(getdate(), 'yyyyMM')
      ,ROW_NUMBER() OVER (ORDER BY CustomerNo ASC) AS SerialNo 
FROM tblCustomer

But the sequence on SerialNo column continues even the month has changed.

Current Output:

--------------------------
|Customer|Month |SerialNo|
|--------+------+--------|
|17000001|201710|00001   |
|17000002|201710|00002   |
|17000003|201710|00003   |
|17000004|201710|00004   |
|17000005|201710|00005   |
|17000001|201710|00006   |
|17000002|201710|00007   |
|17000003|201710|00008   |
|17000004|201710|00009   |
|17000005|201710|00010   |
|17000001|201711|00011   |
|17000002|201711|00012   |
|17000003|201711|00013   |
|17000004|201711|00014   |
|17000005|201711|00015   |
--------------------------

Desired Output:

--------------------------
|Customer|Month |SerialNo|
|--------+------+--------|
|17000001|201710|00001   |
|17000002|201710|00002   |
|17000003|201710|00003   |
|17000004|201710|00004   |
|17000005|201710|00005   |
|17000001|201710|00006   |
|17000002|201710|00007   |
|17000003|201710|00008   |
|17000004|201710|00009   |
|17000005|201710|00010   |
|17000001|201711|00001   |
|17000002|201711|00002   |
|17000003|201711|00003   |
|17000004|201711|00004   |
|17000005|201711|00005   |
--------------------------

Please help. Thank you in advance.

Upvotes: 2

Views: 496

Answers (3)

Mahesh.K
Mahesh.K

Reputation: 901

You can do something like this

INSERT INTO tblSerialNo
select customerno,[Month],
ROW_NUMBER() OVER (partition by [Month] ORDER BY CustomerNo ASC) AS SerialNo  from 
(
SELECT CustomerNo ,FORMAT(getdate(), 'yyyyMM')as [Month] FROM tblCustomer
)as a

Upvotes: 1

nazmul.3026
nazmul.3026

Reputation: 998

INSERT INTO tblSerialNo
SELECT CustomerNo
      ,FORMAT(getdate(), 'yyyyMM')
      ,ROW_NUMBER()OVER(Partition By FORMAT(getdate(), 'yyyyMM') Order By CustomerNo ASC)As SerialNo 
FROM tblCustomer

Upvotes: 0

Yosi Dahari
Yosi Dahari

Reputation: 6999

Add PARTITION BY before your ORDER BY clause:

INSERT INTO tblSerialNo
SELECT CustomerNo
      ,FORMAT(getdate(), 'yyyyMM')
      ,ROW_NUMBER() OVER (PARTITION BY FORMAT(getdate(), 'yyyyMM') ORDER BY CustomerNo ASC) AS SerialNo 
FROM tblCustomer

Upvotes: 2

Related Questions