March 5, 2020 at 1:17 pm
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
March 5, 2020 at 5:58 pm
I don't think you really want or need to add such a column to your table. But if you need to return such a value for some type of reporting or something, you can do so in a couple ways.
One would be to add an additional column to #TypeOfVisitsMaster to include the unique code letter you want to use, e.g. "F" for "Full Scopes".
And then you can simply concatenate all your values to create the special code column in #VisitsMaster.
SELECT
'V_' + TVM.NewColumnName + '_' + CAST(VM.MemberID AS VARCHAR) + '_' + FORMAT(VM.FromDate, 'ddMMyyyy')
FROM #VisitsMaster AS VM
INNER JOIN #TypeOfVisitsMaster AS TVM ON
VM.TypeOfVisitID = TVM.TypeOfVisitID
Another way would be to use a simple CASE statement in your SELECT statement, instead of the JOIN.
CASE TypeOfVisitID
WHEN 1 THEN 'F'
WHEN 2 THEN 'blabla'
WHEN 3 THEN 'blublu'
...
END
March 5, 2020 at 7:03 pm
Unfortunately I cannot add a new column in the table TypeOfVisitas the table schema is already freezed. No further change is allowed.
So please explain, I can get the first letter of each 'Visit Type' by joining the tables. Also, I need to derive membercode from MemberMaster table as its not a column in VisitsMaster.
Please elaborate on the second option, the CASE way.
Also please tell me how I can accommodate this code, in my stored proc ( already given) for saving the visit data.
March 5, 2020 at 8:52 pm
The only thing that appears to be missing from the procedure is the memberid. If this is only for one user, you can get that value from the membermaster table and store that in a variable. Then you can use the data to create the member_visit value. You have the from date, the memberid from the other table, the visit type as a parameter. You can put this in your merge statement.
BTW, I wouldn't use merge here. You're either doing an update or insert, so just do an update or insert. Either probably work, but it's my preference to keep things simple.
March 6, 2020 at 1:54 am
I wouldn't add this identifier column to your table at all. It can just be derived from existing data across multiple tables. What happens if they change the name of Visit Type 2 from "Thematic" to "Exotic"? Then all your hard-coded values will be wrong...? That's why it's best to store the code (F, T, S, etc.) in the same table with the VisitTypes. But you say you can't do that, so...
You also can't take the first letter from each type because as you will notice you already have two types beginning with "F". So you are already starting to see the problems with this request and it hasn't even been implemented yet. I would advise them to re-think this... But here's what you can do in the meantime:
SELECT
'V_' +
CASE TypeOfVisitID
WHEN 1 THEN 'FS' -- Full Scopes
WHEN 2 THEN 'T' -- Thematic
WHEN 3 THEN 'S' -- Supervisory
WHEN 4 THEN 'I' -- Incident
WHEN 5 THEN 'FU' -- Follow-Up
ELSE 'O' -- Others
END + '_' +
CAST(MemberID AS VARCHAR) + '_' +
FORMAT(FromDate, 'ddMMyyyy') AS VisitCode,
*
FROM #VisitsMaster
March 9, 2020 at 5:34 pm
Can you join to the other tables within the Merge statement so you have access to your codes?
This might be easier to implement as a CTE, but I don't like them.
This gives F for two different visit types, but there are ways to get around that. Append the first letter following a space for Full Scope would give you FS.
MERGE VisitsMaster AS T
USING (SELECT @MemberID, @convertedFromDate, @convertedToDate, @TypeOfVisitID,
CONCAT('V_', LEFT(b.TypeOfVisit,1), '_', a.MemberCode, '_', @convertedFromDate,@convertedToDate) as VisitMasterCode
FROM #MemberMaster AS a
CROSS JOIN #TypesOFVisitsMaster AS b
WHERE @MemberID = a.MemberID
AND @TypeOfVisitID = b.TypeOfVisitID
) AS S (MemberID, FromDate ,ToDate ,TypeOfVisitID, VisitMasterCode )
ON (T.MemberID = S.MemberID and T.FromDate = S.FromDate and T.ToDate = S.ToDate and T.TypeOfVisitID = S.TypeOfVisitID )
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply