RE: increment values on day basis on column

SSC Guru

Points: 186127

August 28, 2012 at 3:18 am

Generating your own identity values isn't as straightforward as it might at first appear, the code for preventing concurrency issues is fairly complex. Why don't you use the identity column supported by SQL Server and generate this pseudokey on the fly?

;WITH SampleData AS (

SELECT ID, InsertDateDT = CAST(InsertDate AS DATETIME)

FROM (VALUES (1,'20120828'), (2,'20120828'), (3,'20120829'),(4,'20120829')) d (ID, InsertDate)

)

SELECT

ID,

InsertDateDT,

MyBigintCompositeID = CAST(

CONVERT(VARCHAR(8),InsertDateDT,112)

+ RIGHT('00000'+CAST(ROW_NUMBER() OVER(PARTITION BY InsertDateDT ORDER BY ID) AS VARCHAR(5)),5)

AS BIGINT)

FROM SampleData

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden