If Else + ...

[tmeers]

Ok I am trying to create a unique number for all names in a table. It's a fairly simple task but I'm having issues with it. So far I have this

Select isNull(Upper(left(PtFirstName,1)),'') + isNull(Upper(left(PtLastName,1)),'') 
+ '000' +
Convert(varchar(10),pt_id) 
+ (left(ptssn,2))
 
as UName
from Patient

But if ptssn is null it returns a null result, I was hoping to swap it with the last two digits of the person date of birth but am unable to figure it out.

Second part of my troubles are the + '000' + I want make it a 4 or 5 digit number but some of the pt_id's are already 5 digits. So if >=99 I would add 3 zero's and if >=999 I would add only 2 zeros and if >=9999 I would add only 1 zero. Anything above 9999 would be fine as it is. I tried using a CASE to do it but cannot figure it out.

Hoping some one has a quick solution to this issue.

Tim

[tmeers]

Well I got part of it figure out and I figured out how to update the already existing table. Here's what I have so far:

CREATE TABLE #temptbl(
pid int, RandNUmber varchar(15))
 
Insert into #temptbl
    (pid, RandNUmber) 
Select pt_id as pid, isNull(Upper(left(PtFirstName,1)),'') + isNull(Upper(left(PtLastName,1)),'') 
    + '000' + Convert(varchar(10),pt_id) + isnull(left(ptssn,2),(Upper(left(ptdob,2)))) as RandNUmber
from Patient
 
update Patient
set PtRandNumber = (select #temptbl.RandNUmber from #temptbl where #temptbl.pid = Patient.Pt_id)
 
Drop Table #temptbl

I still need to figure out the zero's part though and thats a biggie.

[iburyak]

Try this:

Select pt_id as pid, isNull(Upper(left(PtFirstName,1)),'') + isNull(Upper(left(PtLastName,1)),'') 
    + right('00000' + Convert(varchar(10),pt_id), 5) + isnull(left(ptssn,2),(Upper(left(ptdob,2)))) as RandNUmber
from Patient

Good Luck.

[tmeers]

Try this:

Select pt_id as pid, isNull(Upper(left(PtFirstName,1)),'') + isNull(Upper(left(PtLastName,1)),'') 
    + right('00000' + Convert(varchar(10),pt_id), 5) + isnull(left(ptssn,2),(Upper(left(ptdob,2)))) as RandNUmber
from Patient

Good Luck.

Oh that is perfect. Thank you very much for your help.
Tim