SQL Server Conditional Sequence Number

Question

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.

what does INSERT INTO tblSerialNo suppose to do?

Sal-laS
– Sal-laS

2017-11-01 06:42:39 +00:00
Commented Nov 1, 2017 at 6:42 — Sal-laS
– Sal-laS, Commented Nov 1, 2017 at 6:42

Yosi Dahari · Accepted Answer · 2017-11-01 06:42:47Z

2

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

answered Nov 1, 2017 at 6:42

Yosi Dahari

7,0395 gold badges26 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sal-laS Over a year ago

can u explain how insert and select work together?

Mahesh.K · Accepted Answer · 2017-11-01 06:47:36Z

1

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

answered Nov 1, 2017 at 6:47

Mahesh.K

9016 silver badges16 bronze badges

Comments

nazmul.3026 · Accepted Answer · 2017-11-01 06:46:06Z

0

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

answered Nov 1, 2017 at 6:46

nazmul.3026

1,0081 gold badge10 silver badges22 bronze badges

Collectives™ on Stack Overflow

SQL Server Conditional Sequence Number

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related