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.CCRAWaitingLists
(
--authoritative source:
--A06.CDWWork.CCRA.pa_waitinglist


--fields
WaitingListRowNumber bigint NOT NULL, --CCRA assigned (PK), from wl_rowid
WaitingListTypeID bigint, --wl_waitlisttype_dr
HospitalRowNumber bigint, --wl_refdephosp_dr
ConsultUID varchar(15), --from wl_referraluniqueid
ReferralNumber varchar(15), --from wl_no
AppointmentDate datetime2(7), --wl_apptdate
AppointmentTime time(7), --wl_appttime (combine AppointmentDate and AppointmentTime ?)


--primary key
CONSTRAINT PK_CCRAWaitingLists PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
WaitingListRowNumber ASC --will need to be 100% sure this is unique in source
)
)
ON CoreData
--when using clustered columnstore index, can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE)
GO

--foreign keys
ALTER TABLE dbo.CCRAWaitingLists
ADD CONSTRAINT FK_CCRAWaitingLists_WaitingListTypeID
FOREIGN KEY (WaitingListTypeID)
REFERENCES dbo.CCRAWaitingListTypes (WaitingListTypeID)

GO

ALTER TABLE dbo.CCRAWaitingLists
ADD CONSTRAINT FK_CCRAWaitingLists_HospitalRowNumber
FOREIGN KEY (HospitalRowNumber)
REFERENCES dbo.CCRAHospitals (HospitalRowNumber)

GO

--indexes



--TODO: Are the traditional non-clustered indexes still needed?
CREATE NONCLUSTERED INDEX IX_CCRAWaitingLists_ConsultUID ON dbo.CCRAWaitingLists
(
ConsultUID
) 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_CCRAWaitingList_ReferralNumber ON dbo.CCRAWaitingLists
(
ReferralNumber
) 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_CCRAWaitingLists_AppointmentDate ON dbo.CCRAWaitingLists
(
AppointmentDate
) 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, auto-generated by HSRM',
N'SCHEMA', N'dbo',
N'TABLE', N'CCRAWaitingLists',
N'COLUMN', N'WaitingListRowNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'FK to CCRAWaitinglistTypes join field for pac_waitinglisttype wlt_rowid in source',
N'SCHEMA', N'dbo',
N'TABLE', N'CCRAWaitingLists',
N'COLUMN', N'WaitingListTypeID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'FK to CCRAHospitals',
N'SCHEMA', N'dbo',
N'TABLE', N'CCRAWaitingLists',
N'COLUMN', N'HospitalRowNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'UniqueConsultUID from HSRM CCRA Views, sample: 660_3917519 / station + Consult',
N'SCHEMA', N'dbo',
N'TABLE', N'CCRAWaitingLists',
N'COLUMN', N'ConsultUID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Referral number; auto-generated by HSRM when referral is created',
N'SCHEMA', N'dbo',
N'TABLE', N'CCRAWaitingLists',
N'COLUMN', N'ReferralNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'First Appointment Date for referral (non-canceled status)',
N'SCHEMA', N'dbo',
N'TABLE', N'CCRAWaitingLists',
N'COLUMN', N'AppointmentDate'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'First Appointment Time for referral (non-canceled status)',
N'SCHEMA', N'dbo',
N'TABLE', N'CCRAWaitingLists',
N'COLUMN', N'AppointmentTime'
GO