asked on

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

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?

Shaun Kline

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

mankowitz

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';

mankowitz

shaun: beat me by one second!

Becky Edwards

ASKER

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.

ASKER CERTIFIED SOLUTION

Shaun Kline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Becky Edwards

ASKER

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