how to reset the seqence order day to day?

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

friends here i need to generate day to day sequence order by using date.... for example: today my first id sequence is 2015/06/25/001 and tomorrow my first id will be 2015/06/26/001..please explain with examples
thank you.....

Posted 25-Jun-15 20:38pm

User-11630313

Add a Solution

Comments

Suvendu Shekhar Giri 26-Jun-15 3:40am

You are using both PostgreSQL & SQL Server 2012 for the same application, really ?

User-11630313 26-Jun-15 3:44am

no i am using PostgreSql only....

_Asif_ 26-Jun-15 3:46am

What have you tried?

User-11630313 26-Jun-15 3:49am

generate a sequence with the date and it will be automatically reset the next day

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Answer 1 · 2015-06-25T21:54:00

In SQL Server you can do it like
To get the first number,

SQL

SELECT CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)+'/'+RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2)+'/'+RIGHT('0'+CAST(DATEPART(DAY,GETDATE()) AS VARCHAR),2)+'/'+'001'

You can make it dynamic to check with some field of a table to get latest value like

SQL

DECLARE @OrderSequenceBase VARCHAR(20)
SELECT @OrderSequenceBase= CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)+'/'+RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2)+'/'+RIGHT('0'+CAST(DATEPART(DAY,GETDATE()) AS VARCHAR),2)+'/'

DECLARE @OrderSequenceTrail VARCHAR(3)
SELECT @OrderSequenceTrail=CASE WHEN OrderSequence LIKE @OrderSequenceBase+'%' THEN ISNULL (RIGHT(OrderSequence,3),'0') ELSE '0' END
FROM YourTable
WHERE OrderSequence LIKE @OrderSequenceBase+'%'

SELECT @OrderSequenceBase+RIGHT('00'+CAST(CAST(@OrderSequenceTrail AS INT)+1 AS VARCHAR),3)

Example:

SQL

DECLARE @OrderSequenceBase VARCHAR(20)
SELECT @OrderSequenceBase= CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)+'/'+RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2)+'/'+RIGHT('0'+CAST(DATEPART(DAY,GETDATE()) AS VARCHAR),2)+'/'
 
DECLARE @OrderSequence VARCHAR(20)
SELECT @OrderSequence='2015/06/25/003'
DECLARE @OrderSequenceTrail VARCHAR(3)
SELECT @OrderSequenceTrail=CASE WHEN @OrderSequence LIKE @OrderSequenceBase+'%' THEN ISNULL (RIGHT(@OrderSequence,3),'0') ELSE '0' END
 

SELECT @OrderSequenceBase+RIGHT('00'+CAST(CAST(@OrderSequenceTrail AS INT)+1 AS VARCHAR),3)

I have no idea about PostgreSQL but the approach should work.

Hope, it helps :)