Skip to content

Archieve DB Process

DataBaseName: HPMC

Archieve DataBaseName: Hpmc_Archieve

Archieve Condition: Based on Discharge date

Overview

The following queries and steps will guide you how to get Archieve process Accountnumbers and how to implement process of archieving Database..

Step1:

In this step we will get Archieve AccountNumber's and those are inserted into a table called ArchieveAccountNoList.

1
2
insert into ArchieveAccountNoList(AccountNo)
select AccountNumber from Visits where DischargeDate is not null and DischargeDate<'2018-01-01'

Step2:

Archieve Table Based on AccountNumber:

Let us take an example for Archieveing a table like NursingReport by using following guidelines. Getting AccountNumber's which are going to be Archieve to NursingReport table and those are inserted to a table called ArchieveFacilityData. 

1
2
3
insert into ArchieveFacilityData(AccountNo,TableName,RStatus)
select distinct hl.AccountNo,'NursingReport',0 from NursingReport nr inner join 
ArchieveAccountNoList al on nr.AccountNo=hl.AccountNo where al.RStatus=0

Note

The above process Step2 should be repeat for remaining HPMC Arhieve Tables(Based on AccountNumber's) List that was given at end of this file.

Step3:

Archieve Table Based on DocumentId:

Let us take an example for Archieveing a table like DateDict by using following guidelines. Getting DocumentId's which are going to be Archieve to DateDict table and those are inserted to a table called ArchieveFacilityData_DocumentId. 

1
2
3
4
insert into ArchieveFacilityData_DocumentId(DocumentId,TableName,RStatus)
select distinct di.JobId,'DateDict',0 from DocumentInfo di 
inner join ArchieveAccountNoList al on di.AccountNo=al.AccountNo
inner join DateDict dd on dd.docId=di.JobId

Note

The above process Step3 should be repeat for remaining HPMC Arhieve Tables(Based on DocumentId) List that was given at end of this file.

Step4:

Before going to Start Archieve process you should insert Archieve AccountNumber's data into ArchieveDatabase which is Hpmc_Archieve and repective that table.

Step5:

Once Archieve AccountNuber's data insertion process completed than only you an start the archieve process by using following queries.

Archieve Deletion Process For SectionHeaders(Based on AccountNumber) :

1
2
3
Select count(AccountNo) from SectionHeaders where AccountNo in (select AccountNo from ArchieveFacilityData where TableName='SectionHeaders' and RStatus=0)
delete from SectionHeaders where AccountNo in (select AccountNo from ArchieveFacilityData where TableName='SectionHeaders' and RStatus=0)
Update ArchieveFacilityData set RStatus=1 where TableName='SectionHeaders' and RStatus=0

Note

The above deletion process should be repeat for remaining HPMC Arhieve Tables(Based on AccountNumber's) List.

Archieve Deletion Process For DateDict(Based on DocumentId) :

1
2
3
Select count(JobId) from DateDict where JobId in (select DocumentId from ArchieveFacilityData_DocumentId where TableName='DateDict' and RStatus=0)
delete from DateDict where JobId in (select DocumentId from ArchieveFacilityData_DocumentId where TableName='DateDict' and RStatus=0)
Update ArchieveFacilityData_DocumentId set RStatus=1 where TableName='DateDict' and RStatus=0

Note

The above deletion process should be repeat for remaining HPMC Arhieve Tables(Based on DocumentId) List.

Step6:

Archieve Deletion Process For QueryAlert's :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
--Based on NoteId Deletion For MemoReceiver
delete from MemoReceiver where NoteId in (
select distinct mr.NoteId from Memoinfo mf
inner join ArchieveAccountNoList al on al.AccountNo=mf.AccountNo
inner join MemoReceiver mr on mr.noteid=mf.noteid
)

--QAAnalysis
select * from QAAnalysis where LastModify<'2018-01-01' order by 1
Delete from QAAnalysis where LastModify<'2018-01-01'

--CDSMetrics_PhysicianActivity
select * from CDSMetrics_PhysicianActivity where Emonth<=12 and EYear<=2017 order by 1
Delete from CDSMetrics_PhysicianActivity where Emonth<=12 and EYear<=2017

--Deletion For FieldDescription
insert into ArchieveFacilityData_DocumentId(DocumentId,TableName,RStatus,Condition)
select distinct dc.Qid,'FieldDescription',0,'Qid' from DiagnosisCriteria dc
inner join ArchieveAccountNoList al on dc.AccountNo=al.AccountNo
inner join FieldDescription fd on fd.Qid=dc.Qid

Step7:

Archieve Tables List Based on AccountNumber:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
NursingReport
FDKWReferences
EncphAnnotations
signorsymptom
snomedTerms
Icd10TempDisease
LabResults
KeyAnnotations
Emelements_temp
MeditechVitals
SectionHeaders
IQFormResults
KPAIDxDisease
LabReference
DocIPMedications
ReimbursementInfo
viewdocumentreviewlog
HCAsymptoms
SuggestiveIcdCodes
HashCodeIQDetails
QAExpectedDRG
Medications
Icd10TempDisease2
Meditechcodes
MTPsiResults
DNMTPsiResults
PDOCDetails
QueryAlertResults
DNPsiResults
PsiResults
PhysicianDetails
Day1Vitals
FacilityIqiResults
IQIResults
DRGChangeLog
Radsymptoms
ALLICDDRGS
Icd10icdpriority
IQAnnotations
KPAIDxDisease2
EncphDataByViewMain
DxFactors
PCSBodyPart
DNFacilityIQIResults
VitalSigns
DNIQIResults
KPAIDxCoding
PCSQualifier
EMCodeResult
RadImpression
CDSMetrics_QueryDetails
CodeChangeLog
Proceduredate
EMlevelService
DxCombResults
PCSOperation
TotalOperationInfo
AccountReview
AllConcurrentICDDrgs
ExcludeIcdcodes
ConcurrentReview2
ConcurrentDxCoding
LogEntry
MeditechAdmitOrders
DocHomeMedications
ICD10ProcDocs
FacilityDRG
insertVseriesFinalData
CDSPriorityScoreByAccount
PatientVisits2
ArchieveAccountNoList
CDSPriorityDRGInfoByAccount
DocumentRunningLog
PCSCombinations
Icd10procpriority
IQCriteriaMetData
AccountLockingInfo
iqCurrentcensus
fxannotations2
PCSDevice
KPAIProcDocs
HPMCLiveAccountNoList
DeletedDCQueries
DocImmunization
DocAllergies
AdmitOrders
PCSApproach
ConcurrentPCSCoding
fxannotations1
KPAIPCSCoding
CDSMetrics_AccountDetails
CDSAccountReminders
CDSQueryAnnotations
AccountFollowup
QueryResponse
QueryImpactDRGDetails
POAChangeLog
CDSAuditLog
QueryDocCreationLog
DocHabits
EditQuerylog
QueryImpactDetails
CurrentCensusAccNos

Archieve Tables List Based on DocumentId:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
Icd10AnnotationElements
LabContent
labdata1
VSigns
AnnotationList
PExam
PlanRecord
fxvalues
PExamContent
Plan4
reviewdata
procedurepcs
ICD10habitsdata
ReviewOfSystems
PatientInfo
Icd10AllergiesData
DischargeCondition
ROSNeg

Archieve Tables List For QuerAlert's:

1
2
3
4
5
6
7
8
9
FDKeywords
FieldDescription
FDLabs
FDVitals
FDMeds
CDSMetrics_PhysicianActivity
QAAnalysis
MemoReceiver
MemoInfo

Archieve Tables List at Ending(Dependent):

1
2
3
4
5
6
7
DateDict
DocumentInfo
DiagnosisCriteria
QueryAlertsRerunrunLog
HL7Insurance
Patients
Visits

Step8

Indexing:

After completion of Archieve Process you should do indexing that Archieve Database like HPMC as well as MedmineMaster by Executing the following cursor.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
declare @QryString varchar(500)
declare @TBLName varchar(100)
Begin

    declare IndexingByFacility cursor for   
    SELECT distinct object_name(object_id) from sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null) where index_id <> 0 and avg_fragmentation_in_percent > 10   
    open IndexingByFacility
    fetch next from IndexingByFacility into @TBLName
    while (@@fetch_Status<>-1)  
    begin           
            SET @QryString='ALTER INDEX ALL ON '+@TBLName+' rebuild WITH (FILLFACTOR = 80)'
            --print @QryString              
            exec(@QryString)                    
        fetch next from IndexingByFacility into @TBLName
    end  
    close IndexingByFacility 
    deallocate IndexingByFacility