Generating a unique code for a table column value & saving it in the table

Transact-SQL
,
#1

I have the following tables:

create table #MemberMaster(MemberID INT, MemberCode nvarchar(50), MemberName nvarchar(150))
insert into #MemberMaster
select 1, 'MEM001', 'Member1' union
select 2, 'MEM002', 'Member2' union
select 3, 'MEM003', 'Member3' union
select 4, 'MEM004', 'Member4' union
select 5, 'MEM005', 'Member5' union
select 6, 'MEM006', 'Member6'

select * from #MemberMaster

create table #TypeOfVisitsMaster(TypeOfVisitID int, TypeOfVisit nvarchar(150))
insert into #TypeOfVisitsMaster
select 1, 'Full Scopes' union
select 2, 'Thematic' union
select 3, 'Supervisory' union
select 4, 'Incident' union
select 5, 'Follow-up' union
select 6, 'Others'

select * from #TypeOfVisitsMaster

create table #VisitsMaster(VisitID int identity(1,1), MemberID int, [FromDate] [date] , [ToDate] [date] , TypeOfVisitID [smallint] )
insert into #VisitsMaster (MemberID,[FromDate],[ToDate],TypeOfVisitID)
select 1, CAST(N'2020-02-06' AS Date), CAST(N'2020-02-13' AS Date), 1 union
select 3, CAST(N'2020-02-04' AS Date), CAST(N'2020-02-05' AS Date), 3 union
select 1, CAST(N'2020-02-03' AS Date), CAST(N'2020-02-19' AS Date), 4 union
select 2, CAST(N'2020-02-18' AS Date), CAST(N'2020-02-20' AS Date), 3 union
select 1, CAST(N'2020-02-03' AS Date), CAST(N'2020-02-05' AS Date), 2 union
select 1, CAST(N'2019-02-01' AS Date), CAST(N'2019-02-02' AS Date), 4

select * from #VisitsMaster

I need to add a column 'Visitcode' in the table #VisitsMaster, which should be a unique visit code generated in the format 'V_VisitType_MemberCode_VisitFromDate' which will be saved to column 'Visitcode' in the table #VisitsMaster.

In the table #VisitsMaster, MemberID,[FromDate],[ToDate],TypeOfVisitID should be saved first, then only the column 'Visitcode' can be generated. That's my understanding.

For a visit, with TypeOfVisitID =1 & MemberID =1 & VisitID =1, the Visitcode should be generated as 'V_F_MEM001_06022020'

where F is 'Full Scopes', the TypeOfVisit for TypeOfVisitID =1, 'MEM001' is Membercode for MemberID =1 & '06022020' is [FromDate] of VisitID =1.

Please help me how I can generate this code & save it in the table.

I have written a stored procedure for the purpose of adding values to the table VisitsMaster

CREATE PROCEDURE [dbo].[AddVisitsMaster]
@MemberID bigint=null
,@FromDate nvarchar(20)=null
,@ToDate nvarchar(20)=null
,@TypeOfVisitID smallint=null
AS
BEGIN


DECLARE @convertedFromDate date, @convertedToDate date,@IsActive bit =1,@IsDeleted bit =0;
SET @convertedFromDate = CONVERT(date, CONVERT(date, @FromDate, 103), 120);
SET @convertedToDate = CONVERT(date, CONVERT(date, @ToDate, 103), 120);

IF NOT EXISTS (select * from VisitsMaster where MemberID=@MemberID and TypeOfVisitID=@TypeOfVisitID 
and FromDate=@convertedFromDate and ToDate=@convertedToDate )

BEGIN
MERGE VisitsMaster AS T
USING (SELECT @MemberID, @convertedFromDate ,@convertedToDate ,@TypeOfVisitID ) AS S 
(MemberID, FromDate ,ToDate ,TypeOfVisitID ) 
ON (T.MemberID = S.MemberID and T.FromDate = S.FromDate and T.ToDate = S.ToDate and T.TypeOfVisitID = S.TypeOfVisitID )

WHEN NOT MATCHED THEN 
INSERT (MemberID ,FromDate ,ToDate ,TypeOfVisitID) 
VALUES (S.MemberID,S.FromDate ,S.ToDate ,S.TypeOfVisitID);
END
ELSE
BEGIN
SELECT 'Record already exists.';
END

END
GO
#2

I want to save the new column "VisitCode" in VisitsMaster table with datatype NVARCHAR(100) after the visit data is saved & visitID is generated as shown in the stored proc.

I have tried this way. Will it be helpful?

SELECT
'V_' +
FROM #VisitsMaster AS V
INNER JOIN #TypeOfVisitsMaster AS TV ON
V.TypeOfVisitID = TV.TypeOfVisitID

But how to generate first letter of Visit type from #TypeOfVisitsMaster & Membercode from memberID saved in #VisitsMaster is where I am having problem.

#3

Please always include drop table? This is possible but not sure if it is valuable/advantageous and not sure why you need to do this

if OBJECT_ID('tempdb..#MemberMaster') is not null
	drop table #MemberMaster
	go

if OBJECT_ID('tempdb..#TypeOfVisitsMaster') is not null
	drop table #TypeOfVisitsMaster
	go

if OBJECT_ID('tempdb..#VisitsMaster') is not null
	drop table #VisitsMaster
	go

create table #MemberMaster(MemberID INT, MemberCode nvarchar(50), 
MemberName nvarchar(150) )
insert into #MemberMaster
select 1, 'MEM001', 'Member1' union
select 2, 'MEM002', 'Member2' union
select 3, 'MEM003', 'Member3' union
select 4, 'MEM004', 'Member4' union
select 5, 'MEM005', 'Member5' union
select 6, 'MEM006', 'Member6'


--Notice Here I added VisitTypeCode UNIQUE(VisitTypeCode)
create table #TypeOfVisitsMaster(TypeOfVisitID int, TypeOfVisit nvarchar(150), 
VisitTypeCode nvarchar(3) constraint VisitTypeCode UNIQUE(VisitTypeCode) )
insert into #TypeOfVisitsMaster(TypeOfVisitID,TypeOfVisit, VisitTypeCode)
select 1, 'Full Scopes', 'FS' union
select 2, 'Thematic', 'T' union
select 3, 'Supervisory', 'S' union
select 4, 'Incident', 'I' union
select 5, 'Follow-up', 'FU' union
select 6, 'Others', 'O'


create table #VisitsMaster(VisitID int identity(1,1), MemberID int, 
[FromDate] [date] , [ToDate] [date] , TypeOfVisitID [smallint] )
insert into #VisitsMaster (MemberID,[FromDate],[ToDate],TypeOfVisitID)
select 1, CAST(N'2020-02-06' AS Date), CAST(N'2020-02-13' AS Date), 1 union
select 3, CAST(N'2020-02-04' AS Date), CAST(N'2020-02-05' AS Date), 3 union
select 1, CAST(N'2020-02-03' AS Date), CAST(N'2020-02-19' AS Date), 4 union
select 2, CAST(N'2020-02-18' AS Date), CAST(N'2020-02-20' AS Date), 3 union
select 1, CAST(N'2020-02-03' AS Date), CAST(N'2020-02-05' AS Date), 2 union
select 1, CAST(N'2019-02-01' AS Date), CAST(N'2019-02-02' AS Date), 4
 
--'V_F_MEM001_06022020'			yyyy-mm-dd to dd-mm-yyy
SELECT 'V_' + VisitTypeCode + '_' + MemberCode + 
 '_' + replace(convert(varchar(10),[FromDate], 105),'-',''), 
[FromDate], convert(varchar(10),[FromDate], 105)
FROM #VisitsMaster AS V
join #MemberMaster m on v.MemberID = m.MemberID
JOIN #TypeOfVisitsMaster AS TV ON
V.TypeOfVisitID = TV.TypeOfVisitID
1 Like