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.Locations
(
--fields
LocationID [int] Identity(1,1) NOT NULL, --CDW LocationSID
StationID smallint NULL, --EPRS assigned (FK), referencess Stations
RegionID tinyint NULL, --EPRS assigned (FK), referencess Regions
InstitutionID tinyint NULL, --EPRS assigned (FK), referencess Institutions
PrimaryStopCodeID int NULL, --EPRS assigned (FK), referencess StopCodes
SecondaryStopCodeID int NULL, --EPRS assigned (FK), referencess StopCodes
RecordStatusID tinyint NULL, --EPRS assigned (Fk), referencess RecordStatuses
BatchLogID bigint NULL, --EPRS assigned (FK), referencess BatchLogs
CDWLocationsysnumber bigint NULL,
LocationIEN bigint NULL,
LocationName varchar(50) NOT NULL,
LocationType varchar(50) NULL,
MedicalService varchar(50) NULL,
PhysicalLocation varchar(50) NULL,
SpecialAMISStop varchar(50) NULL,
CategoryOfVisit varchar(50) NULL,
VisitLocation varchar(50) NULL,
NoncountClinicFlag BIT NULL,
--primary key
CONSTRAINT PK_Locations PRIMARY KEY CLUSTERED
(
LocationID 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
/**
--(<LocationSID, int,>
--,<LocationIEN, varchar(50),>
-- ,<Sta3n, smallint,>
-- ,<LocationName, varchar(50),>
-- ,<LocationType, varchar(50),>
--,<PrimaryStopCodeSID, int,>
--,<SecondaryStopCodeSID, int,>
--,<MedicalService, varchar(50),>
--,<PhysicalLocation, varchar(50),>
--,<CategoryOfVisit, varchar(50),>
,<LocationAbbreviation, varchar(50),>
,<PhoneNumber, varchar(50),>
,<PhoneNumberExtension, varchar(50),>
,<CollateralVisitFlag, char(1),>
,<ProhibitedAccessFlag, char(1),>
,<ClinicAtInstitutionFlag, char(1),>
,<ExtensionLocationTypeSID, int,>
,<ClinicGroupSID, int,>
--,<VisitLocation, varchar(50),>
,<TreatingSpecialtySID, int,>
,<SpecialAMISStop, varchar(50),>
,<DefaultProviderSID, int,>
,<AgencySID, int,>
,<WardLocationSID, int,>
,<AppointmentLength, smallint,>
--,<NoncountClinicFlag, char(1),>
,<InactivationDate, date,>
,<InactivationVistaErrorDate, varchar(50),>
,<InactivationDateTransformSID, bigint,>
,<ReactivationDate, date,>
,<ReactivationVistaErrorDate, varchar(50),>
,<ReactivationDateTransformSID, bigint,>
,<DivisionSID, smallint,>
--,<InstitutionSID, int,>
,<PrincipalLocationSID, int,>
,<PharmacySiteSID, int,>
,<PharmacyActionProfileFlag, char(1),>
,<InpatientMedAdministrationFlag, char(1),>
,<VariableAppointmentLengthFlag, char(1),>
,<HolidayFlag, char(1),>
,<OccasionOfServiceClinicFlag, char(1),>
,<OccasionOfServiceVistaPackageSID, int,>
,<DefaultAppointmentTypeSID, smallint,>
,<CountWorkloadValidationFlag, char(1),>
,<PatientFriendlyLocationName, varchar(100),>
,<DirectPatientSchedulingFlag, char(1),>
,<DisplayAppointmentFlag, char(1),>
,<XRayFilmRequiredFlag, char(1),>
,<AskForCheckInOutTimeFlag, char(1),>
,<PreviousYearStopCodeSID, int,>
,<PreviousYearCreditStopCodeSID, int,>
,<DefaultToPrimaryCarePractitionerFlag, char(1),>
,<NoShowLetterSID, int,>
,<PreAppointmentLetterSID, int,>
,<ClinicCancellationLetterSID, int,>
,<AppointmentCancellationLetterSID, int,>
,<AllowableConsecutiveNoShowAppointment, smallint,>
,<ClinicStartHour, smallint,>
,<SchedulingSlotHoldDays, smallint,>
,<MaximumOverbooksPerDay, smallint,>
,<DisplayIncrementsPerHour, varchar(50),>
,<FutureBookingMaximumDays, smallint,>
,<AutoRebookStartHour, smallint,>
,<AutoRebookMaximumDays, smallint,>
,<AgencyIEN, varchar(50),>
,<AppointmentCancellationLetterIEN, varchar(50),>
,<ClinicCancellationLetterIEN, varchar(50),>
,<ClinicGroupIEN, varchar(50),>
,<DefaultAppointmentTypeIEN, varchar(50),>
,<DefaultProviderIEN, varchar(50),>
,<DivisionIEN, varchar(50),>
,<ExtensionLocationTypeIEN, varchar(50),>
,<InstitutionIEN, varchar(50),>
,<NoShowLetterIEN, varchar(50),>
,<OccasionOfServiceVistaPackageIEN, varchar(50),>
,<PharmacySiteIEN, varchar(50),>
,<PreAppointmentLetterIEN, varchar(50),>
,<PreviousYearCreditStopCodeIEN, varchar(50),>
,<PreviousYearStopCodeIEN, varchar(50),>
,<PrimaryStopCodeIEN, varchar(50),>
,<PrincipalLocationIEN, varchar(50),>
,<SecondaryStopCodeIEN, varchar(50),>
,<TreatingSpecialtyIEN, varchar(50),>
,<WardLocationIEN, varchar(50),>
,<ETLBatchID, int,>
--,<OpCode, char(1),>
,<VistaCreateDate, datetime,>
,<VistaEditDate, datetime,>)
**/
--foreign keys
ALTER TABLE dbo.Locations
ADD CONSTRAINT FK_Locations_StationID
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO
ALTER TABLE dbo.Locations
ADD CONSTRAINT FK_Locations_RegionID
FOREIGN KEY (RegionID)
REFERENCES dbo.Regions (RegionID)
GO
ALTER TABLE dbo.Locations
ADD CONSTRAINT FK_Locations_InstitutionID
FOREIGN KEY (InstitutionID)
REFERENCES dbo.Institutions (InstitutionID)
GO
ALTER TABLE dbo.Locations
ADD CONSTRAINT FK_Locations_PrimaryStopCodeID
FOREIGN KEY (PrimaryStopCodeID)
REFERENCES dbo.StopCodes (StopCodeID)
GO
ALTER TABLE dbo.Locations
ADD CONSTRAINT FK_Locations_SecondaryStopCodeID
FOREIGN KEY (SecondaryStopCodeID)
REFERENCES dbo.StopCodes (StopCodeID)
GO
ALTER TABLE dbo.Locations
ADD CONSTRAINT FK_Locations_RecordStatusID
FOREIGN KEY (RecordStatusID)
REFERENCES dbo.RecordStatuses (RecordStatusID)
GO
ALTER TABLE dbo.Locations
ADD CONSTRAINT FK_Locations_BatchLogID
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO
/**
LocationID [int] Identity(1,1) NOT NULL, --CDW LocationSID
StationID smallint NULL, --EPRS assigned (FK), referencess Stations
RegionID tinyint NULL, --EPRS assigned (FK), referencess Regions
InstitutionID int NULL, --EPRS assigned (FK), referencess Institutions
PrimaryStopCodeID int NULL, --EPRS assigned (FK), referencess StopCodes
SecondaryStopCodeID int NULL, --EPRS assigned (FK), referencess StopCodes
RecordStatusID tinyint NULL, --EPRS assigned (Fk), referencess RecordStatuses
BatchLogID bigint NULL, --EPRS assigned (FK), referencess BatchLogs
**/
--indexes
CREATE NONCLUSTERED INDEX IX_Locations_StationID ON dbo.Locations
(
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 10,000 rows
)
ON DefinitionIndex
GO
CREATE NONCLUSTERED INDEX IX_Locations_RegionID ON dbo.Locations
(
RegionID
) 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 10,000 rows
)
ON DefinitionIndex
GO
--constraints
--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Unique Key for the table',
N'SCHEMA', N'dbo',
N'TABLE', N'Locations',
N'COLUMN', N'LocationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Stationss table',
N'SCHEMA', N'dbo',
N'TABLE', N'Locations',
N'COLUMN', N'StationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Regions table, what most VA uses',
N'SCHEMA', N'dbo',
N'TABLE', N'Locations',
N'COLUMN', N'RegionID'
GO
--FINISH adding the rest of the definitions- JSH