Below is a script that can be used (with caution). If there are partners attached, it may not work. Replace the 1 in @profile = 1 to be the profile id of the interview and replace the 2 in @interivew = 2 with the interview id. We do this with caution as some of the partners may have already been contacted.
USE STDPrism;
GO
DECLARE @profile int
, @interview int
, @diseasecategory varchar(30)
, @removeThisProfileOnly int
, @removeDiseaseLinkToInterview CHAR(1);
SELECT @profile = 1
, @interview = 2
, @diseasecategory = 'Gonorrhea' -- 'Chlamydia' --'HIV' -- 'Syphilis' -- 'Gonorrhea'
, @removeThisProfileOnly = NULL --NULL when you don't want to remove a profile from an interview --184358 -- 1745244
, @removeDiseaseLinkToInterview = 'Y'; --Yes when you want to remove the disease from the Interview
BEGIN TRY
-- Start a transaction that will only commit if all goes well
BEGIN TRANSACTION mytrans;
-- Locate the records that need to be unlinked for a specific disease
SELECT
r.[ID_Profile]
,r.[ID_ProfileLink]
,r.[ID_Relationship]
,rl.ID_FieldRecordLink
,f.DT_Added
,[SecondDiff] = DATEDIFF(second,f.DT_Added,rl.DT_Added)
,f.CD_Disease
,f.CD_ReferralBasis
,f.ID_Disposition
,f.ID_DispositionUser
,[FieldRecordNotes] = (SELECT COUNT(*)
FROM [Prism].[Notes] n WITH(NOLOCK)
WHERE f.ID_FieldRecord = n.ID_FieldRecord
AND f.ID_Profile = n.ID_Profile)
,[FieldRecordSymptom] = (SELECT COUNT(*)
FROM [Prism].[Symptom] s WITH(NOLOCK)
WHERE f.ID_FieldRecord = s.ID_FieldRecord
AND f.ID_Profile = s.ID_Profile)
,[FieldRecordTreatment] = (SELECT COUNT(*)
FROM [Prism].[FieldRecordTreatment] frt WITH(NOLOCK)
WHERE f.ID_FieldRecord = frt.ID_FieldRecord
AND f.ID_Profile = frt.ID_Profile)
,[FieldRecordLab] = (SELECT COUNT(*)
FROM [Prism].[Lab] l WITH(NOLOCK)
WHERE f.ID_FieldRecord = l.ID_FieldRecord
AND f.ID_Profile = l.ID_Profile)
,[FieldRecordMaternal] = (SELECT COUNT(*)
FROM [Prism].[Maternal] m WITH(NOLOCK)
WHERE f.ID_Maternal = m.ID_Maternal
AND f.ID_Profile = m.ID_Profile)
,[FieldRecordCongenital] = (SELECT COUNT(*)
FROM [Prism].[Congenital] ct WITH(NOLOCK)
WHERE f.ID_Congenital = ct.ID_Congenital
AND f.ID_Profile = ct.ID_Profile)
,[FieldRecordInterview] = (SELECT COUNT(*)
FROM [Prism].[Interview] i WITH(NOLOCK)
WHERE f.ID_Interview = i.ID_Interview
AND f.ID_Profile = i.ID_Profile)
,[RelationshipLink] = (SELECT COUNT(*)
FROM [Prism].[RelationshipLink] rl2 WITH(NOLOCK)
WHERE f.ID_FieldRecord = rl2.ID_FieldRecordLink
AND f.ID_Profile = rl2.ID_ProfileLink)
INTO #TempLink
FROM [Prism].[Relationship] r WITH(NOLOCK)
INNER JOIN [Prism].[RelationshipLink] rl WITH(NOLOCK)
ON r.[ID_Relationship] = rl.[ID_Relationship]
INNER JOIN [Prism].[FieldRecord] f WITH(NOLOCK)
ON rl.[ID_FieldRecordLink] = f.[ID_FieldRecord]
WHERE r.id_profile = @profile
and r.id_interview = @interview
and (@diseasecategory IS NULL OR Prism.fn_GetDiseaseCategory(f.CD_Disease) = @diseasecategory)
AND (@removeThisProfileOnly IS NULL OR rl.ID_ProfileLink = @removeThisProfileOnly)
order by 4, r.ID_ProfileLink;
-- Display records to be used
select * from #TempLink;
-- Remove TaskList entries for Field Records created
DELETE FROM Prism.TaskList
WHERE ID_FieldRecord IN (
SELECT ID_FieldRecordLink
FROM #TempLink t
WHERE t.[FieldRecordNotes] = 0
AND t.[FieldRecordSymptom] = 0
AND t.[FieldRecordTreatment] = 0
AND t.[FieldRecordLab] = 0
AND t.[FieldRecordMaternal] = 0
AND t.[FieldRecordCongenital] = 0
AND t.[FieldRecordInterview] = 0
AND t.[RelationshipLink] = 1
AND t.SecondDiff IN (0,1))
;
-- Remove RelationshipLink records that were created
DELETE FROM Prism.RelationshipLink
WHERE ID_FieldRecordLink IN (
SELECT ID_FieldRecordLink
FROM #TempLink t
WHERE t.[FieldRecordNotes] = 0
AND t.[FieldRecordSymptom] = 0
AND t.[FieldRecordTreatment] = 0
AND t.[FieldRecordLab] = 0
AND t.[FieldRecordMaternal] = 0
AND t.[FieldRecordCongenital] = 0
AND t.[FieldRecordInterview] = 0
AND t.[RelationshipLink] = 1
AND t.SecondDiff IN (0,1))
;
-- Add note to linked field record stating why link was removed
INSERT INTO [Prism].[Notes]
([ID_Profile],[ID_FieldRecord],[CD_NoteType],[DS_Notes])
SELECT ID_ProfileLink, ID_FieldRecordLink, 'F'
, 'Relationship with Profile ' + CAST(ID_Profile AS VARCHAR) + ' has been removed as per request.'
FROM #TempLink t
WHERE t.[RelationshipLink] > 1
AND @removeDiseaseLinkToInterview = 'Y'
AND t.SecondDiff > 1
;
-- Remove RelationshipLink records that were created for disease being removed
DELETE FROM Prism.RelationshipLink
WHERE ID_FieldRecordLink IN (
SELECT ID_FieldRecordLink
FROM #TempLink t
WHERE t.[RelationshipLink] > 1
AND @removeDiseaseLinkToInterview = 'Y')
;
-- Remove any notes if attached
DELETE FROM Prism.Notes
WHERE ID_FieldRecord IN (
SELECT ID_FieldRecordLink
FROM #TempLink t
WHERE t.[FieldRecordNotes] > 0
AND t.[FieldRecordSymptom] = 0
AND t.[FieldRecordTreatment] = 0
AND t.[FieldRecordLab] = 0
AND t.[FieldRecordMaternal] = 0
AND t.[FieldRecordCongenital] = 0
AND t.[FieldRecordInterview] = 0
AND t.[RelationshipLink] = 1
AND t.SecondDiff IN (0,1))
;
-- Remove the relationship records if another disease is not attached
DELETE FROM Prism.Relationship
WHERE ID_Relationship IN (
SELECT DISTINCT t.[ID_Relationship]
FROM #TempLink t
LEFT JOIN Prism.RelationshipLink rl
on t.ID_Relationship = rl.ID_Relationship
WHERE t.[FieldRecordNotes] = 0
AND t.[FieldRecordSymptom] = 0
AND t.[FieldRecordTreatment] = 0
AND t.[FieldRecordLab] = 0
AND t.[FieldRecordMaternal] = 0
AND t.[FieldRecordCongenital] = 0
AND t.[FieldRecordInterview] = 0
AND t.[RelationshipLink] = 1
AND t.SecondDiff = 0
AND rl.ID_RelationshipLink IS NULL)
;
-- Remove the autogenerated field records
DELETE FROM Prism.FieldRecord
WHERE ID_FieldRecord IN (
SELECT ID_FieldRecordLink
FROM #TempLink t
WHERE t.[FieldRecordNotes] = 0
AND t.[FieldRecordSymptom] = 0
AND t.[FieldRecordTreatment] = 0
AND t.[FieldRecordLab] = 0
AND t.[FieldRecordMaternal] = 0
AND t.[FieldRecordCongenital] = 0
AND t.[FieldRecordInterview] = 0
AND t.[RelationshipLink] = 1
AND t.SecondDiff IN (0,1))
;
-- Remove the link to the interview for the disease passed if
