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
49 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

22 Experts available now in Live!

Get 1:1 Help Now