MSSQL preformatted running number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

MSSQL preformatted running number

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-01-15 : 21:31:26

Hi,

How can i pre-formatted running number as

Year(2 Char)Mth(2 Char)Type(2 Char)autonumber(6 char)

if year & Mth not same, the autonumber will reset to be 1 again.

Example:

120101000001
120101000002
120101000003
120201000001
120202000002
120302000001

Please Advise.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-15 : 22:51:57

something like



SELECT RIGHT(DATENAME(yy,datefield),2) + RIGHT('00' + CAST(DATEPART(mm,datefield) AS varchar(2)),2) + Type + (1000000 + ROW_NUMBER() OVER (PARTITION BY YEAR(datefield),MONTH(datefiled) ORDER BY DAY(datefield))
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources