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.RequestServices
(
--source:
--A06/SQL52: SPV.Dim.RequestService
--fields
RequestServiceID int IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
StationID smallint NOT NULL, --EPRS assigned (FK), Stations table
RecordStatusID tinyint NULL, --EPRS assigned (FK), RecordStatuses table, OpsCode
BatchLogID bigint NOT NULL, --EPRS assigned (FK), BatchLogs table
CDWRequestServiceSysNumber int NOT NULL, --RequestServiceSID
CDWExtractBatchNumber int NULL, --ExtractBatchNumber
RequestServiceIEN int NOT NULL, --RequestServiceIEN
RequestServiceCategory varchar (15) NULL, --?? --set as part of cleaning
RequestServiceName varchar(100) NOT NULL, --ServiceName
RequestServiceAbbreviation varchar(50) NULL, --ServiceAbbreviation
IsCommunityCareRelated bit NOT NULL, --set by transformation/loading script; see cleaner script
--primary key
CONSTRAINT PK_RequestServices PRIMARY KEY CLUSTERED
(
RequestServiceID ASC
)
)
ON DefinitionData
--when using clustered columnstore index, can't set data compression to PAGE
WITH (DATA_COMPRESSION = PAGE) --less than 10,000 rows, don't need compression
GO
/*
RequestServiceSID int NOT NULL, --CDWRequestServiceSysNumber
RequestServiceIEN varchar(50) NOT NULL, --RequestServiceIEN
Sta3n smallint NULL, --Stations, StationID
ServiceName varchar(100) NULL, --RequestServiceName
UnrestrictedAccessFlag char(1) NULL, --ignore
ProcessParentsForUpdatesFlag char(1) NULL, --ignore
ProcessParentsForNotificationsFlag char(1) NULL, --ignore
ProvisionalDxPrompt varchar(50) NULL, --ignore
ProvisionalDxInput varchar(50) NULL, --ignore
RestrictDefaultReasonEdit varchar(50) NULL, --ignore
NotifyServiceOnDiscontinue varchar(50) NULL, --ignore
ResultManagementUserClassIEN varchar(50) NULL, --ignore
ResultManagementUserClassSID int NULL, --ignore
ResultManagementUserClass varchar(50) NULL, --ignore
ServiceAbbreviation varchar(50) NULL, --RequestServiceAbbreviation
ServiceUsage varchar(50) NULL, --ignore
DisableMessage varchar(100) NULL, --ignore
CreatorStaffIEN varchar(50) NULL, --ignore
CreatorStaffSID int NULL, --ignore
InternalName varchar(100) NULL, --ignore
RelatedTreatingSpecialtyIEN varchar(50) NULL, --ignore
RelatedTreatingSpecialtySID int NULL, --ignore
RelatedTreatingSpecialty varchar(50) NULL, --ignore
RelatedServiceSectionIEN varchar(50) NULL, --ignore
RelatedServiceSectionSID int NULL, --ignore
RelatedServiceSection varchar(50) NULL, --ignore
NotifyStaffIEN varchar(50) NULL, --ignore
NotifyStaffSID int NULL, --ignore
SpecialUpdatesStaffIEN varchar(50) NULL, --ignore
SpecialUpdatesStaffSID int NULL, --ignore
ProstheticsServiceFlag char(1) NULL, --ignore
IFCRoutingInstitutionIEN varchar(50) NULL, --ignore
IFCRoutingInstitutionSID int NULL, --ignore
IFCRoutingInstitution varchar(50) NULL, --ignore
IFCRemoteServiceName varchar(100) NULL, --ignore
IFCCoordinatorStaffIEN varchar(50) NULL, --ignore
IFCCoordinatorStaffSID int NULL, --ignore
AdministrativeFlag char(1) NULL, --ignore
ExtractBatchID int NULL, --CDWExtractBatchNumber
OpCode char(1) NULL, --RecordStatusID
VistaCreateDate datetime2(7) NULL, --ignore
VistaEditDate datetime2(7) NULL, --ignore
--BatchLogID should always be the last of the authoritative source fields listed
--in the data gathered by extractors, it should be the first of the EPRS-specific fields
BatchLogID BIGINT
*/
--foreign keys
ALTER TABLE dbo.RequestServices
ADD CONSTRAINT FK_RequestServices_Stations
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO
ALTER TABLE dbo.RequestServices
ADD CONSTRAINT FK_RequestServices_RecordStatuses
FOREIGN KEY (RecordStatusID)
REFERENCES dbo.RecordStatuses (RecordStatusID)
GO
ALTER TABLE dbo.RequestServices
ADD CONSTRAINT FK_RequestServices_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO
--indexes
CREATE NONCLUSTERED INDEX IX_RequestServices_StationID ON dbo.RequestServices
(
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 --don't need, less than 10,000 rows
)
ON DefinitionIndex
GO
CREATE NONCLUSTERED INDEX IX_RequestServices_RequestServiceCategory ON dbo.RequestServices
(
RequestServiceCategory
) 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 --don't need, less than 10,000 rows
)
ON DefinitionIndex
GO
--constraints
ALTER TABLE RequestServices
ADD CONSTRAINT DF_RequestServices_IsCommunityCareRelated
DEFAULT 0
FOR IsCommunityCareRelated
GO
--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS, source: Dim.RequestService. This file allows services and specialties to be grouped in a hierarchy representing the sites services available.
This grouping capabilitymay be used with Review screens to filter out consults to a service, sub-service, specialty, or sub-specialty of consults/requests. The main entry in this file is the "ALL SERVICES" entry.
Other entriesshould be subordinate in its hierarchy. The "ALL SERVICES" entry is used to display the hierarchy of the hospitalservices when the Clinician ordering a consult is prompted for"Select TO Service/Specialty:" to send the consult to.',
N'SCHEMA', N'dbo',
N'TABLE', N'RequestServices',
N'COLUMN', N'RequestServiceID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Stations table; source: sta3n',
N'SCHEMA', N'dbo',
N'TABLE', N'RequestServices',
N'COLUMN', N'StationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to RecordStatuses table; source: opscode',
N'SCHEMA', N'dbo',
N'TABLE', N'RequestServices',
N'COLUMN', N'RecordStatusID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Batch Logs table',
N'SCHEMA', N'dbo',
N'TABLE', N'RequestServices',
N'COLUMN', N'BatchLogID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW Request service system number; source: RequestServiceSID',
N'SCHEMA', N'dbo',
N'TABLE', N'RequestServices',
N'COLUMN', N'CDWRequestServiceSysNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW Extract Batch number; source: ExtractBatchID',
N'SCHEMA', N'dbo',
N'TABLE', N'RequestServices',
N'COLUMN', N'CDWExtractBatchNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Requuest Service IEN; source: RequestServiceIEN',
N'SCHEMA', N'dbo',
N'TABLE', N'RequestServices',
N'COLUMN', N'RequestServiceIEN'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Category for the Requesting Service (CommCare, Choice-First, Non VA Care)',
N'SCHEMA', N'dbo',
N'TABLE', N'RequestServices',
N'COLUMN', N'RequestServiceCategory'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Name of a service or specialty which may receive consult/requests. This may also be a name which represents a group of servicese, source: RequestServiceName',
N'SCHEMA', N'dbo',
N'TABLE', N'RequestServices',
N'COLUMN', N'RequestServiceName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Abbreviation for the Requesting Service, source: ServiceAbbreviation',
N'SCHEMA', N'dbo',
N'TABLE', N'RequestServices',
N'COLUMN', N'RequestServiceAbbreviation'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifies if the request service is community care-related (1=true/0=false)',
N'SCHEMA', N'dbo',
N'TABLE', N'RequestServices',
N'COLUMN', N'IsCommunityCareRelated'
GO