Customisations made to i2b2

1. Remove primary key from Observation Fact table in Data database

Rationale

The primary key creates a unique constraint on Encounter_Num, Concept_Cd, Provider_Id, Start_Date & Modifier_Code. However, we have genuine data that is duplicated on these fields. For example, a repeat test on the same sample where the first was inconclusive.

Applied To

The customisation has been applied to the following i2b2 instances:

• i2b2_b1_data

Customisation

To apply the customisation, run the following SQL in the i2b2 data database:

ALTER TABLE [dbo].[Observation_Fact] DROP CONSTRAINT [OBSERVATION_FACT_PK]
GO

Roll Back

To roll back the customisation, run the following SQL in the i2b2 data database:

ALTER TABLE [dbo].[Observation_Fact] ADD  CONSTRAINT [OBSERVATION_FACT_PK] PRIMARY KEY NONCLUSTERED 
(
        [Encounter_Num] ASC,
        [Concept_Cd] ASC,
        [Provider_Id] ASC,
        [Start_Date] ASC,
        [Modifier_Cd] ASC
)
GO

2. New versions of data load stored procedures

Rationale

The procedures that come with i2b2 are broken, but are used by the i2b2 mapped importer. Ideally I will write the process into the mapped importer.

Customisation:

CREATE PROCEDURE [dbo].[INSERT_EID_MAP_FROMTEMP_RB] (@tempEidTableName VARCHAR(500),  @upload_id INT,
@errorMsg VARCHAR(MAX) = NULL OUTPUT) 
AS
BEGIN
 declare @existingEncounterNum varchar(32);
 declare  @maxEncounterNum int;
 declare @deleteDuplicateSql nvarchar(MAX);

declare  @sql_stmt  nvarchar(MAX);
 
declare  @disEncounterId varchar(200); 
declare  @disEncounterIdSource varchar(50);
declare  @disPatientMapId varchar(200); 
declare  @disPatientMapIdSource varchar(50);


 BEGIN TRY

--Delete duplicate rows with same encounter and patient combination
set @deleteDuplicateSql = 'with deleteTempDup as (SELECT *,ROW_NUMBER() OVER 
( PARTITION BY encounter_map_id,encounter_map_id_source,encounter_id,encounter_id_source 
  ORDER BY encounter_map_id,encounter_map_id_source,encounter_id,encounter_id_source ) AS RNUM FROM ' + @tempEidTableName +') 
delete  from deleteTempDup where rnum>1';
exec sp_executesql @deleteDuplicateSql;


--set IDENTITY_INSERT encounter_mapping ON; 
 -- get max encounter num
 select @maxEncounterNum = isnull(max(encounter_num),0) from encounter_mapping with (UPDLOCK); 
 -- cursor which iterates distinct encounter_id,encounter_id_source compination
 SELECT @sql_stmt = 'DECLARE my_cur INSENSITIVE CURSOR FOR ' +
              ' SELECT distinct encounter_id,encounter_id_source,patient_map_id,patient_map_id_source from ' +  @tempEidTableName  ;
EXEC sp_executesql @sql_stmt;

OPEN my_cur;

FETCH NEXT FROM my_cur into @disEncounterId, @disEncounterIdSource,@disPatientMapId, @disPatientMapIdSource ;
 WHILE @@FETCH_STATUS = 0
 
 BEGIN 
 BEGIN TRANSACTION
  if  @disEncounterIdSource = 'HIVE'   
   begin
     SET @existingEncounterNum = NULL
    --check if hive number exist, if so assign that number to reset of map_id's within that pid
    select @existingEncounterNum = encounter_num from encounter_mapping where encounter_num = @disEncounterId and encounter_ide_source = 'HIVE';
    
   if @existingEncounterNum is not NULL 
   begin
        set @sql_stmt =  ' update ' + @tempEidTableName  + ' set encounter_num = encounter_id, process_status_flag = ''P'' ' + 
        ' where encounter_id = @pdisEncounterId and not exists (select 1 from encounter_mapping em where em.encounter_ide = encounter_map_id ' + 
        ' and em.encounter_ide_source = encounter_map_id_source)'; 
        EXEC sp_executesql @sql_stmt,N'@pdisEncounterId nvarchar(200)', @pdisEncounterId = @disEncounterId;
    end
    else 
    begin
        -- generate new patient_num i.e. take max(_num) + 1 
        if @maxEncounterNum < @disEncounterId 
        begin
            set @maxEncounterNum = @disEncounterId;
        end;
        set @sql_stmt = ' update ' + @tempEidTableName + ' set encounter_num = encounter_id, process_status_flag = ''P'' where ' +
        ' encounter_id =  @pdisEncounterId and encounter_id_source = ''HIVE'' and not exists (select 1 from encounter_mapping em where em.encounter_ide = encounter_map_id ' +
        ' and em.encounter_ide_source = encounter_map_id_source)'; 
        EXEC sp_executesql @sql_stmt, N'@pdisEncounterId nvarchar(200)',@pdisEncounterId=@disEncounterId ;
     end;    
    -- print ' HIVE ';
 end
 else 
 begin
     SET @existingEncounterNum = NULL
       select @existingEncounterNum = encounter_num  from encounter_mapping where encounter_ide = @disEncounterId and 
        encounter_ide_source = @disEncounterIdSource ; 

       
       if @existingEncounterNum is not  NULL
       begin
            set @sql_stmt =  ' update ' + @tempEidTableName + ' set encounter_num = @pexistingEncounterNum, process_status_flag = ''P'' ' + 
            ' where encounter_id = @pdisEncounterId and not exists (select 1 from encounter_mapping em where em.encounter_ide = encounter_map_id ' +
            ' and em.encounter_ide_source = encounter_map_id_source)' ;
        EXEC sp_executesql @sql_stmt,N'@pexistingEncounterNum int, @pdisEncounterId nvarchar(200)',@pexistingEncounterNum=@existingEncounterNum ,
          @pdisEncounterId=@disEncounterId;
       end
       else
       begin

            set @maxEncounterNum = @maxEncounterNum + 1 ;
             set @sql_stmt =   ' insert into ' + @tempEidTableName + ' (encounter_map_id,encounter_map_id_source,patient_map_id,patient_map_id_source,encounter_id,encounter_id_source,encounter_num,process_status_flag
             ,encounter_map_id_status,update_date,download_date,import_date,sourcesystem_cd) 
             values(@pmaxEncounterNum1,''HIVE'',@pdisPatientMapId, @pdisPatientMapIdSource,@pmaxEncounterNum2,''HIVE'',@pmaxEncounterNum3,''P'',''A'',getdate(),getdate(),getdate(),''edu.harvard.i2b2.crc'')' ;
            EXEC sp_executesql @sql_stmt, N'@pmaxEncounterNum1 int,@pmaxEncounterNum2 int, @pmaxEncounterNum3 int, @pdisPatientMapId varchar(200), @pdisPatientMapIdSource varchar(50)', 
            @pmaxEncounterNum1=@maxEncounterNum,@pmaxEncounterNum2=@maxEncounterNum,@pmaxEncounterNum3=@maxEncounterNum,@pdisPatientMapId=@disPatientMapId, @pdisPatientMapIdSource=@disPatientMapIdSource; 
            
                         set @sql_stmt =   ' update ' + @tempEidTableName +' set encounter_num = @pmaxEncounterNum , process_status_flag = ''P'' ' +  
             ' where encounter_id = @pdisEncounterId and  not exists (select 1 from ' + 
             ' encounter_mapping em where em.encounter_ide = encounter_map_id ' + 
             ' and em.encounter_ide_source = encounter_map_id_source)' ; 
            EXEC sp_executesql @sql_stmt,N'@pmaxEncounterNum int,@pdisEncounterId nvarchar(200)',@pmaxEncounterNum=@maxEncounterNum , @pdisEncounterId=@disEncounterId;
            
       end  ;
    
      -- print ' NOT HIVE ';
 end ; 
commit;
FETCH NEXT FROM my_cur into @disEncounterId, @disEncounterIdSource,@disPatientMapId, @disPatientMapIdSource ;
END ;
CLOSE my_cur
DEALLOCATE my_cur
 BEGIN TRANSACTION 
-- do the mapping update if the update date is old and the encounter_id_source is HIVE
   set @sql_stmt = ' update encounter_mapping set encounter_num = temp.encounter_id,
        patient_ide   =   temp.patient_map_id ,
        patient_ide_source  =   temp.patient_map_id_source ,
        encounter_ide_status    = temp.encounter_map_id_status  ,
        update_date = temp.update_date,
        download_date  = temp.download_date ,
                import_date = getdate() ,
        sourcesystem_cd  = temp.sourcesystem_cd ,
                upload_id = ' + convert(nvarchar,@upload_id) + ' 
                from encounter_mapping em  
         inner join ' + @tempEidTableName + ' temp
                    on em.encounter_ide = temp.encounter_map_id and
                        em.encounter_ide_source = temp.encounter_map_id_source 
        where  temp.encounter_id_source = ''HIVE'' and temp.process_status_flag is null  and
        isnull(em.update_date,0)<= isnull(temp.update_date,0) ' ;
EXEC sp_executesql @sql_stmt;

-- insert new encounters into encounter_mapping 
set @sql_stmt =  ' insert into encounter_mapping (encounter_ide,encounter_ide_source,patient_ide,patient_ide_source,encounter_ide_status,encounter_num,update_date,download_date,import_date,sourcesystem_cd,upload_id) 
    select encounter_map_id,encounter_map_id_source,patient_map_id,patient_map_id_source,encounter_map_id_status,encounter_num,update_date,download_date,getdate(),sourcesystem_cd,' + convert(nvarchar,@upload_id) + ' from ' + @tempEidTableName + '  
    where process_status_flag = ''P'' ' ; 
EXEC sp_executesql @sql_stmt;
commit;
END TRY

BEGIN CATCH
   if @@TRANCOUNT > 0
   begin
      ROLLBACK
   end
   begin try
   DEALLOCATE my_cur
   end try
   begin catch
   end catch  
   declare @errMsg nvarchar(4000), @errSeverity int
   select @errMsg = ERROR_MESSAGE(), @errSeverity = ERROR_SEVERITY();
   set @errorMsg = @errMsg;
   RAISERROR(@errMsg,@errSeverity,1); 
 END CATCH

end;

GO

CREATE PROCEDURE  [dbo].[INSERT_PID_MAP_FROMTEMP_RB] (@tempPatientMapTableName VARCHAR(500), @upload_id INT, 
   @errorMsg varchar(max)  = NULL OUTPUT) 
AS 

BEGIN 
  declare @deleteDuplicateSql nvarchar(MAX),
    @insertSql nvarchar(MAX); 

declare  @existingPatientNum nvarchar(32);
declare @maxPatientNum int;
declare @disPatientId nvarchar(200); 
declare @disPatientIdSource nvarchar(50);
declare @sql nvarchar(MAX);
BEGIN TRY
   

        --Delete duplicate rows with same patient combination
        set @deleteDuplicateSql = 'with deleteTempDup as (SELECT *,ROW_NUMBER() OVER 
( PARTITION BY patient_map_id,patient_map_id_source,patient_id,patient_id_source 
  ORDER BY patient_map_id,patient_map_id_source,patient_id,patient_id_source ) AS RNUM FROM ' + @tempPatientMapTableName +') 
delete  from deleteTempDup where rnum>1';

exec sp_executesql @deleteDuplicateSql;
         
--get max patient_num from patient_mapping table
select @maxPatientNum = isnull(max(patient_num),0) from patient_mapping with (UPDLOCK); 
-- create cursor to iterate distinct event_id,event_id_source combination 
SELECT @sql = 'DECLARE my_cur INSENSITIVE CURSOR FOR ' +
              ' SELECT distinct patient_id,patient_id_source from ' +  @tempPatientMapTableName  ;
EXEC sp_executesql @sql

OPEN my_cur

FETCH NEXT FROM my_cur into @disPatientId, @disPatientIdSource ;
 WHILE @@FETCH_STATUS = 0

 BEGIN 
BEGIN TRANSACTION
   --print @disPatientId + ' ' + @disPatientIdSource      
  if  @disPatientIdSource = 'HIVE'  
  begin
     SET @existingPatientNum = NULL
     --check if hive number exist, if so assign that number to reset of map_id's within that pid
     select @existingPatientNum  = patient_num  from patient_mapping where patient_num = @disPatientId and patient_ide_source = 'HIVE';
   
     if @existingPatientNum IS NOT NULL
     begin 
         --print 'not null'
        set @sql = ' update ' + @tempPatientMapTableName + ' set patient_num = patient_id, process_status_flag = ''P'' ' + 
        ' where patient_id =  @pdisPatientId   and not exists (select 1 from patient_mapping pm where pm.patient_ide = patient_map_id ' + 
        ' and pm.patient_ide_source = patient_map_id_source)' ;  
        EXEC sp_executesql @sql, N'@pdisPatientId nvarchar(200)',  @pdisPatientId = @disPatientId; 
        --EXEC sp_executesql @sql;
        --select @disPatientId;
     end
     else 
     begin
         --print 'null not exist HIVE' + @disPatientId
        -- generate new patient_num i.e. take max(patient_num) + 1 
        if @maxPatientNum < @disPatientId 
        begin
           set @maxPatientNum = @disPatientId;
        end;
        set @sql = ' update ' + @tempPatientMapTableName +' set patient_num = patient_id, process_status_flag = ''P'' where ' +
        ' patient_id = @pdisPatientId and patient_id_source = ''HIVE'' and not exists (select 1 from patient_mapping pm where pm.patient_ide = patient_map_id ' +
        ' and pm.patient_ide_source = patient_map_id_source)';
        EXEC sp_executesql @sql, N'@pdisPatientId nvarchar(200)', @pdisPatientId=@disPatientId;
      end; 
   -- test if record fectched
   --print ' HIVE ';
  end;
 else 
 begin
     SET @existingPatientNum = NULL
       --print 'Not HIVE'
       select @existingPatientNum = patient_num   from patient_mapping where patient_ide = @disPatientId and 
        patient_ide_source = @disPatientIdSource ; 

                --print 'existing PNum' + @existingPatientNum
       
       -- test if record fetched. 
      
       if @existingPatientNum is not NULL
       begin
                  --print 'About to update Temp with P'
          set @sql = ' update ' + @tempPatientMapTableName +' set patient_num = @pexistingPatientNum , process_status_flag = ''P'' ' + 
            ' where patient_id = @pdisPatientId and not exists (select 1 from patient_mapping pm where pm.patient_ide = patient_map_id ' + 
            ' and pm.patient_ide_source = patient_map_id_source)' ; 
            --print @sql
            --print '@pexistingPatientNum = ' + @existingPatientNum
            --print '@pdisPatientId = ' + @disPatientId
            EXEC sp_executesql @sql,N'@pexistingPatientNum int, @pdisPatientId nvarchar(200)',@pexistingPatientNum=@existingPatientNum,@pdisPatientId=@disPatientId;
       end
       else 
       begin
              -- print ' NOT HIVE and not present ' + @disPatientId;
             set @maxPatientNum = @maxPatientNum + 1 ;
             set @sql = 'insert into ' + @tempPatientMapTableName + ' (patient_map_id,patient_map_id_source,patient_id,patient_id_source,patient_num,process_status_flag ' + 
             ',patient_map_id_status,update_date,download_date,import_date,sourcesystem_cd ) ' +  
             ' values(@pmaxPatientNum1,''HIVE'',@pmaxPatientNum2,''HIVE'',@pmaxPatientNum3,''P'',''A'',getdate(),getdate(),getdate(),''edu.harvard.i2b2.crc'')' ;  
             EXEC sp_executesql  @sql ,N'@pmaxPatientNum1 int,@pmaxPatientNum2 int,
                        @pmaxPatientNum3 int',@pmaxPatientNum1 = @maxPatientNum ,@pmaxPatientNum2 = @maxPatientNum,@pmaxPatientNum3 = @maxPatientNum; 
            set @sql =  'update ' + @tempPatientMapTableName + ' set patient_num =  @pmaxPatientNum , process_status_flag = ''P'' ' +  
             ' where patient_id = @pdisPatientId and  not exists (select 1 from ' + 
            ' patient_mapping pm where pm.patient_ide = patient_map_id ' + 
            ' and pm.patient_ide_source = patient_map_id_source)' ; 
             EXEC sp_executesql @sql,N'@pmaxPatientNum int,@pdisPatientId nvarchar(200)',@pmaxPatientNum = @maxPatientNum, @pdisPatientId=@disPatientId  ;
          end;
        
 end ;
commit;
 FETCH NEXT FROM my_cur into @disPatientId, @disPatientIdSource ;
END

CLOSE my_cur
DEALLOCATE my_cur
BEGIN TRANSACTION

-- do the mapping update if the update date is old
   set @sql = ' update patient_mapping set patient_num = temp.patient_id,
        patient_ide_status      = temp.patient_map_id_status  ,
        update_date = temp.update_date,
        download_date  = temp.download_date ,
                import_date = getdate() ,
        sourcesystem_cd  = temp.sourcesystem_cd ,
                upload_id = ' + convert(nvarchar,@upload_id) + ' 
                from patient_mapping pm 
        inner join ' + @tempPatientMapTableName + ' temp
        on  pm.patient_ide = temp.patient_map_id and pm.patient_ide_source = temp.patient_map_id_source
        where temp.patient_id_source = ''HIVE'' and temp.process_status_flag is null  and isnull(temp.update_date,0) >= isnull(pm.update_date,0)';

EXEC sp_executesql @sql;

set @sql = ' insert into patient_mapping (patient_ide,patient_ide_source,patient_ide_status,patient_num,update_date,download_date,import_date,sourcesystem_cd,upload_id) ' + 
    ' select patient_map_id,patient_map_id_source,patient_map_id_status,patient_num,update_date,download_date,getdate(),sourcesystem_cd,' + convert(nvarchar,@upload_id) + ' from '+@tempPatientMapTableName+  
    ' where process_status_flag = ''P'' ' ;
EXEC sp_executesql @sql;

commit;
 
 END TRY
BEGIN CATCH
   if @@TRANCOUNT > 0 
  begin
     ROLLBACK
   end
   
   begin try
   DEALLOCATE my_cur
   end try
   begin catch
   end catch 
   declare @errMsg nvarchar(4000), @errSeverity int
   select @errMsg = ERROR_MESSAGE(), @errSeverity = ERROR_SEVERITY();
   set @errorMsg = @errMsg;
   RAISERROR(@errMsg,@errSeverity,1); 
 END CATCH
END;

GO