Computed Column Formula and how it can work base on condition
I have this challenge. I want to achieve a formula in a computed column. Currently my formula is: ((((([sponsorID]+' ')+substring(CONVERT([varchar],datepart(year,[DateCreated]),(0)),(3),(4)))+'-')+'0')+CONVERT([varchar],[ID],(0)))
Where: sponsorID, DateCreated, ID are columns in my table PatientInfo.
A sample of what the formular produce is PVT 11 10567
My ID column auto increment
Now The total no of record in my table called PatientInfo is getting to 500,000. and the formula i was given is [SPONSORCODE YEAR - 4 DIGIT]. It must not exceed 4 digit!
How do i achieve this formula in such a way that the id stops when a year ends and start afresh in a new year. i.e. patient folder no can be PVT 10 0128 ....... and PVT 11 0128. this is because in a year the total no of new patients will not exceed 4 digits. aside from the existing patients.
PLEASE Kindly assist. i hope i have provide a clear picture of my challenge. if there is a better way to achieve this, please assist.
awaiting your reply.
Would it work to use the last 4 digits of ID? For the ID part of the expression, you could use:
RIGHT('000' + CONVERT(varchar,[ID]),4)
Of course, you could get duplicates if you ever inserted more than 9999 rows in a particular year.
You can't using a computed column based on ID
You'd need a separate incrementing column per year and per sponsor that allows only 0000-9999. Then, use this in your computed column.
In the next version of SQL Server, you can use a sequence. Until then, you could use a sequence emulator: http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx