Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Need to create a unique id, using todays date is YYMMDDHHMM format, adding sequential number and text added

Posted on 2016-07-14
Medium Priority
66 Views
The unique id has to be this
Patient Refund (PR) +Year(YY) + Month(MM) + Day(DD) + Hour(HH) + Minute(MM) + Sequence Number/Count (001)
Example:
2/13/2016 7:30AM record number 16 = "PR" & 160213 & 0730 & 016 = PR1602130730016

I have this so far:  CONCAT('PR',(CONVERT(VARCHAR, GETDATE()-1, 112)))
but that just returns PR20160713

I have tested all the date formats listed on the internet and none of them bring up what I want.

Is this even possible?
0
Question by:Becky Edwards
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2

LVL 27

Expert Comment

ID: 41711065
The script below will get you to the point of the sequence, but we'll need to know more information about the sequence number.

``````DECLARE @testDate DATETIME

SET @testDate = GETDATE()

PRINT 'PR' + CONVERT(VARCHAR(6), @testDate, 12) + REPLACE(CONVERT(VARCHAR(5), @testDate, 108), ':', '')
``````
1

LVL 24

Expert Comment

ID: 41711070
select  'PR' +
right(year(getdate()),2) +
right('0' + cast(month(getdate()) as varchar(2)), 2) +
right('0' + cast(day(getdate()) as varchar(2)), 2) +
right('0' + cast(datepart(hh, getdate()) as varchar(2)), 2) +
right('0' + cast(datepart(mm, getdate()) as varchar(2)), 2) +
'016';
0

LVL 24

Expert Comment

ID: 41711073
shaun: beat me by one second!
0

Author Comment

ID: 41712980
So the sequence would start with 001 and count from there for each line in the query.  Each line should represent a single row in the table.  Not sure yet whether they are going to want me to group by patient or transaction or invoice.  I won't know for sure until testing phase.

Mankowitz:  Your code returned 1607150907 where Shaun's returned 0955 which is the exact hh and mm when I ran it.   I ran yours at the same time so I don't know what happened there.  I like Shaun's better because of that though.
0

LVL 27

Accepted Solution

Shaun Kline earned 2000 total points
ID: 41713249
Generally speaking, I would not recommend calculating the sequence number when the query is run. If SQL Server does not order the result set in the same order each time the query is run, you could generate a unique ID that doesn't always point to the same refund record each run.

It would be a wiser idea to have the sequence number generated and stored when the record is added to the table. From there you would only need to read it back in your query.

As for left zero-padding the sequence number, if it is an integer data type, you can use:
RIGHT('000' + CONVERT(varchar(3), <sequence field>), 3)
1

Author Comment

ID: 41796023
How was this abandoned?  I accepted the solution the next day. (7/15)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month9 days, 7 hours left to enroll