Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I want to set patientid format like 2015/04/1,2,3. How can i do it? What datatype should use? After month change, it automatically set to new month like 2015/05/1,2,3.
Posted
Comments
Suvendu Shekhar Giri 6-Apr-15 6:33am    
More details required ! What are 2 and 3 in 1,2,3? What do mean by "automatically set to new month"?
prashbari 6-Apr-15 6:39am    
Year/month will be same but only numbers will change for whole month. After month changes it again set to new month. 1,2,3 are automatically increment for the same month.
Nikunj Bhanushali 6-Apr-15 10:47am    
To store patient id like yyyy/MM/1 you have to use data type NVARCHAR. Also, if you use it as primary key, you can’t set it auto generated id as it is custom string value. So, better you keep primary key INT id and use more reference column PatientId and store number in whatever format you want.

Unless you are going to do any manipulation on that particular data, it's better to use VARCHAR or NVARCHAR datatype with proper validation.

If you want to store data in the format Year/Month/{patient_no}, you can create a simple function and call that when required.

I haven't tested it, but you could do something like-
SQL
CREATE FUNCTION [dbo].[fuGenerateNewPatientId] ()
RETURNS VARCHAR
AS
BEGIN
    DECLARE @NewPatientId VARCHAR(20)
    DECLARE @LastId INT
    IF(EXISTS(SELECT 1 FROM dbo.Patients WHERE PatientId LIKE (CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)+'/'+RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2))+'/'+'%'))
    BEGIN
        SELECT @LastId=MAX(CAST(RIGHT(PatientId,LEN(PatientId)-8) AS INT))
                       FROM dbo.Patients
                       WHERE PatientId LIKE (CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)+'/'+RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2))+'/'+'%'
        SELECT @NewPatientId=CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)+'/'+RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS  VARCHAR),2))+'/'+CAST(@LastId+1 AS VARCHAR)
END
    ELSE
    BEGIN
       SELECT @NewPatientId=CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)+'/'+RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2))+'/'+'1'
    END
    RETURN @NewPatientId
END

It looks little larger but should do your job.

Hope, it helps :)
In case further help is required, please let me know !
 
Share this answer
 
v5
If you want numbers and commas in there, then you have no choice: it has to be a character based value such as NVARCHAR.
SQL
INSERT INTO MyTable (PatientId) VALUES (LEFT(CONVERT(NVARCHAR(10),GETDATE(),111), 8) + '1,2,3')
 
Share this answer
 
Comments
prashbari 6-Apr-15 10:16am    
format Year/Month/{patient_no} 2015/04/1 2015/04/2. This field should be auto increment
OriginalGriff 6-Apr-15 10:33am    
So use a computed column: store the date and an Identity field, and make teh Patient ID a computed column which uses them both.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900