Solved

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

Posted on 2016-07-14
6
52 Views
Last Modified: 2016-09-13
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
Comment
Question by:Becky Edwards
  • 2
  • 2
  • 2
6 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
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), ':', '')

Open in new window

1
 
LVL 24

Expert Comment

by:mankowitz
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

by:mankowitz
ID: 41711073
shaun: beat me by one second!
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:Becky Edwards
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 26

Accepted Solution

by:
Shaun Kline earned 500 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

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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now