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.ConsultActivities
(
--authoritative source:
--SQL52.SPV.ConsultActivity
--fields
ConsultActivityID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
ConsultID bigint NULL, --EPRS assigned (FK), referencess Consults
PatientID bigint Null, --EPRS assigned (FK), referencess Patients
ResponsibleStaffUserID bigINT NULL, --EPRS assigend (FK), references StaffUsers
EnteredByStaffUserID bigINT NULL, --EPRS assigend (FK), references StaffUsers
PreviousAssignedStaffUserID bigINT NULL, --EPRS (FK) references StaffUsers
StationID smallint NULL, --EPRS assigned (FK), references Stations
ActivityTypeID INT NULL, --EPRS assigned (FK), references ActivityTypes
TIUDocumentID int NULL, --EPRS (FK) references TIUDocumentDefintions
RemoteTIUDocumentID INT NULL, --EPRS (FK) references TIUDocumentDefintions
TIUDocumentDefinitionID int null, --EPRS (FK) references TIUDocumentDefintions
FromRequestServiceSectionID int null, --EPRS (FK) references ServiceSections; join to ServiceSectionID
RecordStatusID tinyint NULL, --EPRS assigned (Fk), referencess RecordStatuses
BatchLogID bigint NULL, --EPRS assigned (FK), referencess BatchLogs
CDWConsultActivitySysNumber BIGINT NOT NULL, --ConsultActivitySID
ConsultActivityIEN INT NULL, --CDW COnsultActivityIEN
CDWExtractBatchSysNumber bigint NULL, --extractBatchID
RequestDateTime DATETIME2 (7) NULL, --Date Consult entered into VISTA
ActivityDateTime DATETIME2 (7) NULL, --Date the activity was recorded in Vista
ActivityEntryDateTime DATETIME2 (7) NULL, --More research as CDW definition is vague
RemoteFilingDateTime DATETIME2(7) NULL, --Related to the remote filing of the consult
RemoteResultSta3n VARCHAR(10) NULL, --Moved to the Stations table join on Sta3n = RemoteResultSta3n
RemoteTimeZone VARCHAR (50) NULL, --Time Zone where activity is captured
--primary key
CONSTRAINT PK_ConsultActivityID PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
ConsultActivityID ASC
)
)
ON CoreData
--when using clustered columnstore index, can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE)
GO
/* RAW Fields:
CREATE TABLE [zraw].[_conConsultActivity] (
--[ConsultActivitySID] BIGINT NOT NULL,
--[ConsultActivityIEN] VARCHAR (50) NOT NULL,
--[ConsultSID] BIGINT NULL, --moved to Consults
--[Sta3n] SMALLINT NOT NULL, --Moved to stations
--[PatientSID] INT NULL, --Moved to Patients
--[RequestDateTime] DATETIME2 (7) NULL,
--[RequestVistaErrorDate] VARCHAR (50) NULL, --IGNORE
--[RequestDateTimeTransformSID] BIGINT NULL, --IGNORE
--[RequestDateSID] INT NULL, --bypass unless moving the DIM.Date table to to consult domain??
--[ActivityDateTime] DATETIME2 (7) NULL,
--[ActivityVistaErrorDate] VARCHAR (50) NULL, --IGNORE
--[ActivityDateTimeTransformSID] BIGINT NULL, --IGNORE
--[ActivityDateSID] INT NULL, --bypass unless moving the DIM.Date table to to consult domain??
--[ActivityEntryDateTime] DATETIME2 (7) NULL,
--[ActivityEntryVistaErrorDate] VARCHAR (50) NULL, --IGNORE
--[ActivityEntryDateTimeTransformSID] BIGINT NULL, --IGNORE
--[ActivityTypeSID] INT NULL, --moved to ActivityTypes
--[Activity] VARCHAR (100) NULL, --Moved to the activitytypes table
--[FromRequestServiceSID] INT NULL, --Moved to ServiceSection by FromRequestServiceSectionID
--[FromRequestService] VARCHAR (100) NULL, --Moved to the ServiceSections table = ServiceSectionname with join FromRequestServiceSectionID = ServiceSectionID
--[TIUDocumentSID] BIGINT NULL,
--[RemoteFilingDateTime] DATETIME2 (7) NULL,
--[RemoteFilingVistaErrorDate] VARCHAR (50) NULL, --IGNORE
--[RemoteFilingDateTimeTransformSID] BIGINT NULL, --IGNORE
--[PreviousAssignedStaffSID] INT NULL,
--[RemoteResultSta3n] VARCHAR(10) NULL, --Moved to the Stations table join on Sta3n = RemoteResultSta3n
--[RemoteTimeZone] VARCHAR (50) NULL,
--[RemoteTIUDocumentSID] BIGINT NULL,
--[RemoteServiceName] VARCHAR (100) NULL, --MOved to the ServiceSections table join on
--[ResponsibleStaffSID] INT NULL,
--[EnteredByStaffSID] INT NULL,
--[EnteredByStaffIEN] VARCHAR (50) NULL,
--[ConsultIEN] VARCHAR (50) NOT NULL,
--[FromRequestServiceIEN] VARCHAR (50) NULL,
--[PatientIEN] VARCHAR (50) NULL,
--[PreviousAssignedStaffIEN] VARCHAR (50) NULL,
--[RemoteTIUDocumentIEN] VARCHAR (50) NULL,
--[ResponsibleStaffIEN] VARCHAR (50) NULL,
--[TIUDocumentIEN] VARCHAR (50) NULL,
--[ETLBatchID] BIGINT NULL, --IGNORED due to region specific
--[OpCode] CHAR (1) NOT NULL, -Moved to RecordStatuses Table
--[VistaCreateDate] DATETIME NULL, --IGNORED
--[VistaEditDate] DATETIME NULL, --IGNORED
--[ExtractBatchID] BIGINT NULL,
--[BatchLogID] BIGINT NULL
*/
--foreign keys
--TODO: Determine if there will be a claims table. If so, uncomment the following. Otherwise, assume no FK need on ClaimID
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_ConsultActivities
FOREIGN KEY (ConsultID)
REFERENCES dbo.Consults (ConsultID)
GO
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_Patients
FOREIGN KEY (PatientID)
REFERENCES dbo.Patients (PatientID)
GO
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_ResponsibleStaffUserID
FOREIGN KEY (ResponsibleStaffUserID)
REFERENCES dbo.StaffUsers (StaffUserID)
GO
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_EnteredByStaffUserID
FOREIGN KEY (EnteredByStaffUserID)
REFERENCES dbo.StaffUsers (StaffUserID)
GO
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_PreviousAssignedStaffUserID
FOREIGN KEY (PreviousAssignedStaffUserID)
REFERENCES dbo.StaffUsers (StaffUserID)
GO
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_Stations
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_ActivityTypes
FOREIGN KEY (ActivityTypeID)
REFERENCES dbo.ActivityTypes (ActivityTypeID)
GO
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_TIUDocumentID
FOREIGN KEY (TIUDocumentID)
REFERENCES dbo.TIUDocuments (TIUDocumentID)
GO
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_RemoteTIUDocumentID
FOREIGN KEY (RemoteTIUDocumentID)
REFERENCES dbo.TIUDocuments (TIUDocumentID)
GO
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_TIUDocumentDefinitions
FOREIGN KEY (TIUDocumentDefinitionID)
REFERENCES dbo.TIUDocumentDefinitions (TIUDocumentDefinitionID)
GO
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_RecordStatuses
FOREIGN KEY (RecordStatusID)
REFERENCES dbo.RecordStatuses (RecordStatusID)
GO
ALTER TABLE dbo.ConsultActivities
ADD CONSTRAINT FK_ConsultActivities_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO
----indexes
-- --Create clustered columnstore index that doesn't exist yet
-- CREATE CLUSTERED COLUMNSTORE INDEX CCIX_FBCSUBClaims ON dbo.ConsultActivities
-- WITH
-- (
-- /*DROP_EXISTING = ON --if the index doesn't exist and this is active,
-- -- the code will go boom
-- ,*/ MAXDOP = 1 --while 0 uses max degrees of parallelism, 1 in
-- -- this case helps tighten the ordering when
-- -- initially using a traditional clustered index
-- , COMPRESSION_DELAY = 10 --in minutes; increase to 60 if doing OLTP to
-- --maintain data in deltarowgroup for 60 minutes
-- , DATA_COMPRESSION = COLUMNSTORE --or COLUMNSTORE_ARCHIVE
-- )
-- ON CoreData --keep the clustered columnstore with the data filegroup rather than index filegroup
-- GO
----TODO: Are the traditional non-clustered indexes still needed?
CREATE NONCLUSTERED INDEX IX_ConsultActivities_PatientID ON dbo.ConsultActivities
(
PatientID
) 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 = PAGE --use page compression; default = NONE
)
ON CoreIndex
GO
CREATE NONCLUSTERED INDEX IX_ConsultActivities_StationID ON dbo.ConsultActivities
(
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 = PAGE --use page compression; default = NONE
)
ON CoreIndex
GO
CREATE NONCLUSTERED INDEX IX_ConsultActivities_RecordStatusID ON dbo.ConsultActivities
(
RecordStatusID
) 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 = PAGE --use page compression; default = NONE
)
ON CoreIndex
GO
CREATE NONCLUSTERED INDEX IX_ConsultActivities_ActivtyTypeID ON dbo.ConsultActivities
(
ActivityTypeID
) 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 = PAGE --use page compression; default = NONE
)
ON CoreIndex
GO
--POSSIBLE INDEX on REQUESTDATETIME?? or ACTIVITYDATETIME??
--CREATE NONCLUSTERED INDEX IX_ConsultActivities_ActivtyTypeID ON dbo.ConsultActivities
-- (
-- ActivityTypeID
-- ) 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 = PAGE --use page compression; default = NONE
-- )
-- ON CoreIndex
--GO
----constraints
----Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key; unique to EPRS; source table is SPV.Con.ConsultActivity',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'ConsultActivityID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to ConsultActivities table, source field: ConsultSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'ConsultID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Patients table, source field: PatientSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'PatientID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, relates to StaffUsers table, EPRS value, source field: ResponsibleStaffSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'ResponsibleStaffUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, relates to StaffUsers table, EPRS value, source field: EnteredStaffSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'EnteredByStaffUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to StaffUsers table, source field: PreviousAssignedStaffUserSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'PreviousAssignedStaffUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Stations table, source field: Sta3n',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'StationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to ConsultActivities table, source field: ActivityTypeSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'ActivityTypeID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to TIUDocuments table, source field: TIUDocumentSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'TIUDocumentID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to TIUDocuments table, source field: RemoteTIUDocumentSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'RemoteTIUDocumentID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to TIUDocumentDefinitions table, source field: RemoteTIUDocumentDefinitionSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'TIUDocumentDefinitionID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to ServiceSections table, source field: FromRequestSErviceSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'FromRequestServiceSectionID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to RecordStatuses table, source field: RecordStatusCode',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'RecordStatusID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Batchlogs table',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'BatchLogID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'VISTA assigned station specific unique patient identifier, source field: ConsultActivityIEN',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'ConsultActivityIEN'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'This is the date and time the order was released from OE/RR.If the TO service entered the order stub through the Add Order (AD)action, this is the service-specified request date.',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'RequestDateTime'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW Date the activity was recorded in Vista',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'ActivityDateTime'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW captured date',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'ActivityEntryDateTime'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Related to the remote filing of the consult',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'RemoteFilingDateTime'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW assigned national ID; source: ExtractBatchID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'CDWExtractBatchsysnumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Time Zone where activity is captured',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultActivities',
N'COLUMN', N'RemoteTimeZone'
GO
--**/