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