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.OneConsults
(
--fields
OneConsultID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
StationID smallint NOT NULL, --FK to Stations
StatusTypeID tinyint NOT NULL, --FK to Status Types
RequestServiceID int NOT NULL, --FK to Request Services
BatchLogID bigint NULL, --FK to batchlogs
CDWConsultSysNumber bigint NOT NULL, --ID field from source Con.Consult
ProvisionalDiagnosisText varchar(255) NULL, --ICD text field from source Con.Consult / CPRS
ProvisionalDiagnosisCode varchar(50) NULL, --ICD code drop down field from source Con.Consult / CPRS
HasProvisionalDiagnosis bit NOT NULL,
FileEntryDateTime datetime2(7) NULL, ---from source Con.Consult
DateSynchronized datetime2(7) NULL,


--primary key
CONSTRAINT PK_OneConsults PRIMARY KEY CLUSTERED
(
OneConsultID ASC
)
)
ON CoreData
--when using clustered columnstore index, can't set data compression to PAGE
WITH (DATA_COMPRESSION = PAGE)
GO

--foreign keys
ALTER TABLE dbo.OneConsults
ADD CONSTRAINT FK_OneConsults_Stations
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO

ALTER TABLE dbo.OneConsults
ADD CONSTRAINT FK_OneConsults_StatusTypes
FOREIGN KEY (StatusTypeID)
REFERENCES dbo.StatusTypes (StatusTypeID)
GO

ALTER TABLE dbo.OneConsults
ADD CONSTRAINT FK_OneConsults_RequestServices
FOREIGN KEY (RequestServiceID)
REFERENCES dbo.RequestServices (RequestServiceID)
GO

ALTER TABLE dbo.OneConsults
ADD CONSTRAINT FK_OneConsults_BatchLog
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO


--indexes

CREATE NONCLUSTERED INDEX IX_OneConsults_ProvisionalDiagnosisText ON dbo.OneConsults
(
ProvisionalDiagnosisText
) 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_OneConsults_ProvisionalDiagnosisCode ON dbo.OneConsults
(
ProvisionalDiagnosisCode
) 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_OneConsults_FileEntryDateTime ON dbo.OneConsults
(
FileEntryDateTime
) 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
ALTER TABLE dbo.OneConsults
ADD CONSTRAINT DF_OneConsults_HasProvisionalDiagnosis
DEFAULT 0
FOR HasProvisionalDiagnosis
GO

--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS',
N'SCHEMA', N'dbo',
N'TABLE', N'OneConsults',
N'COLUMN', N'OneConsultID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Stations Table',
N'SCHEMA', N'dbo',
N'TABLE', N'OneConsults',
N'COLUMN', N'StationID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Status Types table, values like: ACTIVE, CANCELLED, COMPLETE, PENDING, SCHEDULED',
N'SCHEMA', N'dbo',
N'TABLE', N'OneConsults',
N'COLUMN', N'StatusTypeID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Request Services table, values like:CHOICE-FIRST, NON VA CARE, COMMUNITY CARE',
N'SCHEMA', N'dbo',
N'TABLE', N'OneConsults',
N'COLUMN', N'RequestServiceID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'ID field from source Con.Consult',
N'SCHEMA', N'dbo',
N'TABLE', N'OneConsults',
N'COLUMN', N'CDWConsultSysNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'ICD text field from source Con.Consult / CPRS',
N'SCHEMA', N'dbo',
N'TABLE', N'OneConsults',
N'COLUMN', N'ProvisionalDiagnosisText'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'ICD code drop down field from source Con.Consult / CPRS',
N'SCHEMA', N'dbo',
N'TABLE', N'OneConsults',
N'COLUMN', N'ProvisionalDiagnosisCode'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'consult start datetime from source Con.Consult',
N'SCHEMA', N'dbo',
N'TABLE', N'OneConsults',
N'COLUMN', N'FileEntryDateTime'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Date of synchronization with external sources',
N'SCHEMA', N'dbo',
N'TABLE', N'OneConsults',
N'COLUMN', N'DateSynchronized'
GO