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.ConsultFactors
(
--source:
--A06/SQL52.SPV.Con.ConsultFactor

--fields
ConsultFactorID bigint IDENTITY(1,1) NOT NULL,
StationID smallint NOT NULL, --EPRS assigned (FK), reference Stations
ConsultID bigint NOT NULL, --EPRS assigned (FK), reference consults
ConsultActivityID bigint NOT NULL, --EPRS assigned (FK), reference consultActivities
SConsultActivityCommentID bigint NOT NULL, --EPRS assigned (FK), reference SConsultActivityComments
ConsultFactorTypeID smallint NULL, --EPRS assigned (FK), reference ConsultFactors
PatientID bigint NULL, --EPRS assigned (FK), reference Patients
StaffUserID bigint NULL, --EPRS assigned (FK), reference StaffUsers
RecordStatusID tinyint NULL, --EPRS assigned (FK), references RecordStatuses
BatchLogID bigint NOT NULL, --EPRS assigned (FK), reference BatchLogs

[FactorData] VARCHAR (4000) NULL,
[DateFactorText] VARCHAR (100) NULL,

CDWExtractBatchsysNumber bigint NOT NULL, --extractBatchID
ActivityDateTime datetime2(7) NULL,
DateFactorDateTime datetime2(7) NULL,

CTBItemFlag BIT NULL,
/**
[ConsultFactorSID] BIGINT NOT NULL,
[Sta3n] SMALLINT NOT NULL,
-- [ConsultIEN] VARCHAR (50) NOT NULL,
--[ConsultSID] BIGINT NOT NULL,
-- [ConsultActivityIEN] VARCHAR (50) NOT NULL,
--[ConsultActivitySID] BIGINT NOT NULL,
-- [SConsultActivityCommentSID] BIGINT NOT NULL,
--[ConsultFactorTypeID] INT NULL, --ConsultFactorTypenumber
-- [ConsultFactorTypeSID] INT NULL,
--[PatientSID] INT NULL,
--[EnteredByStaffSID] INT NULL,
[ActivityDateTime] DATETIME2 (7) NULL,
--[ConsultFactorType] VARCHAR (50) NULL,
[FactorData] VARCHAR (4000) NULL,
[DateFactorText] VARCHAR (100) NULL,
[DateFactorDateTime] DATETIME2 (7) NULL,
[CTBItemFlag] CHAR (1) NULL,
--[PatientIEN] VARCHAR (50) NULL,
--[EnteredByStaffIEN] VARCHAR (50) NULL,
--[ETLBatchID] INT NULL,
--[OpCode] CHAR (1) NULL,
--[ETLCreateDate] DATETIME2 (7) NULL,
--[ETLEditDate] DATETIME2 (7) NULL,
[ExtractBatchID] BIGINT NOT NULL,
[BatchLogID] BIGINT NULL

**/


--primary key
CONSTRAINT PK_ConsultFactors PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
ConsultFactorID ASC
)
)
ON CoreData
--when using clustered columnstore index, can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE)
GO

--unique key (if any)
--)
--ON StagingData
-- --when using clustered columnstore index, can't set data compression to PAGE
-- WITH (DATA_COMPRESSION = PAGE)

--GO
/*
--<ConsultFactorSID, bigint, -- --[ConsultFactorID PK]
--,<Sta3n, smallint, -- --[Migrated as StationID]
--,<ConsultIEN, varchar(50),> --[Station specific Consult ID; translated as Consultnumber]
--,<ConsultSID, bigint,> -- --[Migrated as ConsultID; sytem unique]; CDWConsultsysnumber
--,<ConsultActivityIEN, varchar(50),> --[Running count of Activity entries per consultID; translated as ConsultActivityNumber]
--,<ConsultActivitySID, bigint,> -- --[Unique ID per activity entry per consultID]
--,<SConsultActivityCommentSID, bigint,> -- --[Unique ID per Activity comment per Activity per consultID]
--,<ConsultFactorTypeID, int,> --[created as COnsultFactorTypeNumber- station unique]
--,<ConsultFactorTypeSID, int,>-- --[ConsultFactorTypeID EPRS FK]; CDWConsultsysnumber
--,<PatientSID, int,> -- --[Use PatientID to pull from Patient Table EPRS FK]
--,<EnteredByStaffSID, int,> -- --[FactorEnteredbyStaffID EPRS FK]; CDWEnteredbystaffsysnumber

--,<ActivityDateTime, datetime2(0),> --[Date of the Activity]
--,<ConsultFactorType, varchar(50),> --MOVED TO ConsultFactorTypes Table [Three (3-5) character designation for Toolbox selected item]
--,<FactorData, varchar(4000),> --REMOVED will capture factor searchstring in consultfactortypetable[Text Data captured that seeded the ConsultFactorType]
--,<DateFactorText, varchar(100),> --[Removed using DateFactorDateTime]
-- ,<DateFactorDateTime, datetime2(0),> --[Date and time the factor was entered/recorded]
--,<CTBItemFlag, char(1),> --[0 or 1 for Item created in the Consult Toolbox]
--,<PatientIEN, varchar(50),> --[Removed using PatientID to get from Patient Table]
--,<EnteredByStaffIEN, varchar(50),> --[station unique identifier for Staff member entering factor; translated as EnteredStaffNumber]
--,<ExtractBatchID, int,> --extractBatchID; Source CDW extractBatchID
--,<OpCode, char(1),> --MOVED To the ReordStatuses Table [1= Missing or Unknown;I = no changes made to record; U= Updated at least once; D = Records marked in Vista as Deleted; P = place holder; S = Special Seeded Records by BISL]
--,<ETLCreateDate, datetime,> --Removed use extractBatchid to join for original ETL create date
--,<ETLEditDate, datetime,> --Removed use SourceBatchNumber to join for original ETL edit date
*/
--foreign keys
ALTER TABLE dbo.ConsultFactors
ADD CONSTRAINT FK_ConsultFactors_Stations
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO

ALTER TABLE dbo.ConsultFactors
ADD CONSTRAINT FK_ConsultFactors_Consults
FOREIGN KEY (ConsultID)
REFERENCES dbo.Consults (ConsultID)
GO

ALTER TABLE dbo.ConsultFactors
ADD CONSTRAINT FK_ConsultFactors_ConsultActvities
FOREIGN KEY (ConsultActivityID)
REFERENCES dbo.ConsultActivities (ConsultActivityID)
GO

ALTER TABLE dbo.ConsultFactors
ADD CONSTRAINT FK_ConsultFactors_SConsultActivityComments
FOREIGN KEY (SConsultActivityCommentID)
REFERENCES dbo.SConsultActivityComments (SConsultActivityCommentID)
GO

ALTER TABLE dbo.ConsultFactors
ADD CONSTRAINT FK_ConsultFactors_ConsultFactorTypes
FOREIGN KEY (ConsultFactorTypeID)
REFERENCES dbo.ConsultFactorTypes (ConsultFactorTypeID)
GO

ALTER TABLE dbo.ConsultFactors
ADD CONSTRAINT FK_ConsultFactors_Patients
FOREIGN KEY (PatientID)
REFERENCES dbo.Patients (PatientID)
GO

ALTER TABLE dbo.ConsultFactors
ADD CONSTRAINT FK_ConsultFactors_StaffUsers
FOREIGN KEY (StaffUserID)
REFERENCES dbo.StaffUsers (StaffUserID)
GO

ALTER TABLE dbo.ConsultFactors
ADD CONSTRAINT FK_ConsultFactors_RecordStatuses
FOREIGN KEY (RecordStatusID)
REFERENCES dbo.RecordStatuses (RecordStatusID)
GO

ALTER TABLE dbo.ConsultFactors
ADD CONSTRAINT FK_ConsultFactors_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO

--indexes
--Create clustered columnstore index that doesn't exist yet
--CREATE CLUSTERED COLUMNSTORE INDEX CCIX_ConsultFactors ON dbo.ConsultFactors
--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 CCIX_ConsultFactors_StationID ON dbo.ConsultFactors
(
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_ConsutlFactors_StaffUserID ON dbo.ConsultFactors
(
StaffUserID
) 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.ConsultFactor',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'ConsultFactorID'
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'ConsultFactors',
N'COLUMN', N'StationID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Consults table; source field: consultSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'ConsultID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to ConsultActivities table; source field: ConsultActivitySID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'ConsultActivityID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, relates to SConsultActivityComments table, EPRS value; source field: SConsultActivityCommentSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'SConsultActivityCommentID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to ConsultFactorTypes table; source field: ConsultFactorTypeSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'ConsultFactorTypeID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, relates to Patients table, EPRS value, source field: PatientSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'PatientID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to StaffUsers table, source field: PatientSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'StaffUserID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to RecordStatuses table',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'RecordStatusID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to BatchLogs table, source field: BatchLogID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'BatchLogID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW Extract Batch Number, source field: extractBatchID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'CDWExtractBatchSysNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'EPRS value, source field: ActivityDateTime',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'ActivityDateTime'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'EPRS value, source field: DateFactorDateTime',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'DateFactorDateTime'
GO


EXECUTE sp_addextendedproperty
N'MS_Description', N'source field: CTBItemFlag, 0 or 1 for Item created in the Consult Toolbox',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactors',
N'COLUMN', N'CTBItemFlag'
GO