Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?CREATE TABLE dbo.TIUDocuments
--Source table dim.tiudocumentdefintion sql52
(
--fields
TIUDocumentID [int] Identity(1,1) NOT NULL, --CDW TIUDocumentsID
ParentTIUDocumentID bigint NULL, --
TIUDocumentDefinitionID int null, --EPRS (FK) references TIUDocumentDefintions
ParentTIUDocumentDefinitonID int NULL,
PatientID bigint NOT Null, --EPRS assigned (FK), referencess Patients
StationID smallint NULL, --EPRS assigned (FK), references Stations
InstitutionID tinyint NOT NULL, --EPRS assigned (FK), references Institutions
DocumentLocationID int NULL, --EPRS assigned (FK), references Locations
ConsultID bigint NULL, --EPRS assigned (FK), references Consults
TIUOrderStatusID tinyint NULL, --EPRS assigned (FK), references OrderStatuses
ReportUrgencyID smallint NULL, --EPRS assigned (FK), references Urgencies
SignedByStaffUserID bigint NULL, --EPRS assigned (FK), references StaffUsers
RecordStatusID tinyint NULL, --EPRS assigned (Fk), referencess RecordStatuses
BatchLogID bigint NULL, --EPRS assigned (FK), referencess BatchLogs
CDWTIUDocumentsysnumber bigint NULL,
CDWParentTIUDocumentsysnumber bigint NULL,
EntryDateTime datetime2(7) NULL,
EpisodeBeginDateTime datetime2(7) NULL,
EpisodeEndDateTime datetime2(7) NULL,
SignatureDateTime datetime2(7) NULL,
ReferenceDateTime datetime2(7) NULL,
VerificationDateTime datetime2(7) NULL,
DeletionDateTime datetime2(7) NULL,
CDWExtractBatchsysNumber int NULL,
TIUDocumentIEN varchar(50) NULL,
VisitType varchar(50) NULL,
DeletionReason varchar(50) NULL,
DocumentSubject varchar(100) NULL,
ProcedureSummaryCode varchar(50)
--primary key
CONSTRAINT PK_TIUDocuments PRIMARY KEY CLUSTERED
(
TIUDocumentID ASC
)
)
ON DefinitionData
--when using clustered columnstore index can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE) --less than 10000 rows don't need compression
GO
/**
--TIUDocumentSID bigint
--,TIUDocumentIEN varchar(50)
--,Sta3n smallint
--,TIUDocumentDefinitionSID int
--,PatientSID int
,VisitSID bigint
--,ParentTIUDocumentDefinitionSID int
--,TIUStatusSID int
--,ParentTIUDocumentSID bigint
--,EpisodeBeginDateTime datetime2(0)
,EpisodeBeginVistaErrorDate varchar(50)
,EpisodeBeginDateTimeTransformSID bigint
,EpisodeBeginDateSID int
--,EpisodeEndDateTime datetime2(0)
,EpisodeEndVistaErrorDate varchar(50)
,EpisodeEndDateTimeTransformSID bigint
--,ReportUrgency varchar(50)
,CreditStopCodeOnCompletionFlag char(1)
,DischargeDateCorrectionFlag char(1)
--,EntryDateTime datetime2(0)
,EntryVistaErrorDate varchar(50)
,EntryDateTimeTransformSID bigint
,AuthorDictatorStaffSID int
,ExpectedSignerStaffSID int
--,DocumentLocationSID int
,SecondaryVisitSID bigint
,ExpectedCosignerStaffSID int
,AttendingPhysicianStaffSID int
,CPRSOrderSID bigint
--,VisitType varchar(50)
--,InstitutionSID int
--,ReferenceDateTime datetime2(0)
,ReferenceVistaErrorDate varchar(50)
,ReferenceDateTimeTransformSID bigint
,ReferenceDateSID int
,EnteredByStaffSID int
,CaptureMethod varchar(50)
,ReleaseDateTime datetime2(0)
,ReleaseVistaErrorDate varchar(50)
,ReleaseDateTimeTransformSID bigint
--,VerificationDateTime datetime2(0)
,VerificationVistaErrorDate varchar(50)
,VerificationDateTimeTransformSID bigint
,VerifiedByStaffSID int
,DictationDateTime datetime2(0)
,DictationVistaErrorDate varchar(50)
,DictationDateTimeTransformSID bigint
,SuspenseDateTime datetime2(0)
,SuspenseVistaErrorDate varchar(50)
,SuspenseDateTimeTransformSID bigint
,PatientTransferSID bigint
,TreatingSpecialtySID int
,TIUIncompleteRecordSID bigint
,ServiceSectionSID int
--,ConsultSID bigint
,RetractedOriginalTIUDocumentSID bigint
--,SignatureDateTime datetime2(0)
,SignatureVistaErrorDate varchar(50)
,SignatureDateTimeTransformSID bigint
,SignatureMode varchar(50)
--,SignedByStaffSID int
,SignedOnChartByStaffSID int
,CosignatureNeededFlag char(1)
,CosignatureDateTime datetime2(0)
,CosignatureVistaErrorDate varchar(50)
,CosignatureDateTimeTransformSID bigint
,CosignatureMode varchar(50)
,CosignedByStaffSID int
,CosignedOnChartByStaffSID int
,AmendmentDateTime datetime2(0)
,AmendmentVistaErrorDate varchar(50)
,AmendmentDateTimeTransformSID bigint
,AmendedByStaffSID int
,AmendmentSignedDateTime datetime2(0)
,AmendmentSignedVistaErrorDate varchar(50)
,AmendmentSignedDateTimeTransformSID bigint
,AdministrativeClosureDateTime datetime2(0)
,AdministrativeClosureVistaErrorDate varchar(50)
,AdministrativeClosureDateTimeTransformSID bigint
,AdministrativeClosureMode varchar(50)
,ArchivePurgeDateTime datetime2(0)
,ArchivePurgeVistaErrorDate varchar(50)
,ArchivePurgeDateTimeTransformSID bigint
,DeletedByStaffSID int
--,DeletionDateTime datetime2(0)
,DeletionVistaErrorDate varchar(50)
,DeletionDateTimeTransformSID bigint
--,DeletionReason varchar(50)
--,DocumentSubject varchar(100)
,InterdisciplinaryParentTIUDocumentSID bigint
--,ProcedureSummaryCode varchar(50)
,ProcedureDateTime datetime2(0)
,ProcedureVistaErrorDate varchar(50)
,ProcedureDateTimeTransformSID bigint
,AmendedByStaffIEN varchar(50)
,AttendingPhysicianStaffIEN varchar(50)
,AuthorDictatorStaffIEN varchar(50)
,ConsultIEN varchar(50)
,CosignedByStaffIEN varchar(50)
,CosignedOnChartByStaffIEN varchar(50)
,CPRSOrderIEN varchar(50)
,DeletedByStaffIEN varchar(50)
,DocumentLocationIEN varchar(50)
,EnteredByStaffIEN varchar(50)
,ExpectedCosignerStaffIEN varchar(50)
,ExpectedSignerStaffIEN varchar(50)
,InstitutionIEN varchar(50)
,InterdisciplinaryParentTIUDocumentIEN varchar(50)
,ParentTIUDocumentDefinitionIEN varchar(50)
,ParentTIUDocumentIEN varchar(50)
,PatientIEN varchar(50)
,PatientMovementIEN varchar(50)
,RetractedOriginalTIUDocumentIEN varchar(50)
,SecondaryVisitIEN varchar(50)
,ServiceSectionIEN varchar(50)
,SignedByStaffIEN varchar(50)
,SignedOnChartByStaffIEN varchar(50)
,TIUDocumentDefinitionIEN varchar(50)
,TIUIncompleteRecordIEN varchar(50)
,TIUStatusIEN varchar(50)
,TreatingSpecialtyIEN varchar(50)
,VerifiedByStaffIEN varchar(50)
,VisitIEN varchar(50)
,ETLBatchID int
,OpCode char(1)
,VistaCreateDate datetime
,VistaEditDate datetime
,ExtractBatchID int
,TIUDocumentDefinition varchar(50) --pulling from the DIM.TIUDocumentDefintions table on a join; this is the only field we need
,BatchLogID bigint
**/
--foreign keys
ALTER TABLE dbo.TIUDocuments
ADD CONSTRAINT FK_TIUDocuments_TIUDocumentDefinitionID
FOREIGN KEY (TIUDocumentDefinitionID)
REFERENCES dbo.TIUDocumentDefinitions (TIUDocumentDefinitionID)
GO
ALTER TABLE dbo.TIUDocuments
ADD CONSTRAINT FK_TIUDocuments_StationID
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO
ALTER TABLE dbo.TIUDocuments
ADD CONSTRAINT FK_TIUDocuments_ParentTIUDocumentID
FOREIGN KEY (TIUDocumentID)
REFERENCES dbo.TIUDocuments (TIUDocumentID)
GO
ALTER TABLE dbo.TIUDocuments
ADD CONSTRAINT FK_TIUDocuments_Patient
FOREIGN KEY (PatientID)
REFERENCES dbo.Patients (PatientID)
GO
ALTER TABLE dbo.TIUDocuments
ADD CONSTRAINT FK_TIUDocuments_InstitutionID
FOREIGN KEY (InstitutionID)
REFERENCES dbo.Institutions (InstitutionID)
GO
ALTER TABLE dbo.TIUDocuments
ADD CONSTRAINT FK_TIUDocuments_DocumentLocationID
FOREIGN KEY (DocumentLocationID)
REFERENCES dbo.Locations (LocationID)
GO
ALTER TABLE dbo.TIUDocuments
ADD CONSTRAINT FK_TIUDocuments_ConsultID
FOREIGN KEY (ConsultID)
REFERENCES dbo.Consults (ConsultID)
GO
ALTER TABLE dbo.TIUDocuments
ADD CONSTRAINT FK_TIUDocuments_TIUStatusID
FOREIGN KEY (TIUOrderStatusID)
REFERENCES dbo.OrderStatuses (OrderStatusID)
GO
ALTER TABLE dbo.TIUDocuments
ADD CONSTRAINT FK_TIUDocuments_ReportUrgencyID
FOREIGN KEY (ReportUrgencyID)
REFERENCES dbo.Urgencies (UrgencyID)
GO
ALTER TABLE dbo.TIUDocuments
ADD CONSTRAINT FK_TIUDocuments_SignedByStaffUserID
FOREIGN KEY (SignedByStaffUserID)
REFERENCES dbo.StaffUsers (StaffUserID)
GO
ALTER TABLE dbo.TIUDocuments
ADD CONSTRAINT FK_TIUDocuments_RecordStatusID
FOREIGN KEY (RecordStatusID)
REFERENCES dbo.RecordStatuses (RecordStatusID)
GO
--indexes
CREATE NONCLUSTERED INDEX IX_TIUDocuments_StationID ON dbo.TIUDocuments
(
StationID
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism controls how many CPUs to use; 0 = default (all available) max = 64
DATA_COMPRESSION = NONE --don't need less than 10000 rows
)
ON DefinitionIndex
GO
--constraints
--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS' ,
N'SCHEMA', N'dbo',
N'TABLE', N'TIUDocuments' ,
N'COLUMN', N'TIUDocumentID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to TIUDocuments table',
N'SCHEMA', N'dbo',
N'TABLE', N'TIUDocuments',
N'COLUMN', N'ParentTIUDocumentID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to TIUDocumentDefinitions table',
N'SCHEMA', N'dbo',
N'TABLE', N'TIUDocuments',
N'COLUMN', N'TIUDocumentDefinitionID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Definitions table' ,
N'SCHEMA', N'dbo' ,
N'TABLE', N'TIUDocuments' ,
N'COLUMN', N'ParentTIUDocumentDefinitonID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Patients table' ,
N'SCHEMA', N'dbo' ,
N'TABLE', N'TIUDocuments' ,
N'COLUMN', N'PatientID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Stations table' ,
N'SCHEMA', N'dbo' ,
N'TABLE', N'TIUDocuments' ,
N'COLUMN', N'StationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Institutions table' ,
N'SCHEMA', N'dbo' ,
N'TABLE', N'TIUDocuments' ,
N'COLUMN', N'InstitutionID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Locations table' ,
N'SCHEMA', N'dbo' ,
N'TABLE', N'TIUDocuments' ,
N'COLUMN', N'DocumentLocationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Consults table' ,
N'SCHEMA', N'dbo' ,
N'TABLE', N'TIUDocuments' ,
N'COLUMN', N'ConsultID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Statuses table' ,
N'SCHEMA', N'dbo' ,
N'TABLE', N'TIUDocuments' ,
N'COLUMN', N'TIUOrderStatusID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Batch table' ,
N'SCHEMA', N'dbo' ,
N'TABLE', N'TIUDocuments' ,
N'COLUMN', N'BatchLogID'
GO