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.FBCSClaimDiagnoses
(
--authoritative sources:
--A06.CDWWork.FBCS.HCFA
--,<box211, varchar(8000),> , Diag Code 1
--,<box212, varchar(8000),> , Diag Code 2
--,<box213, varchar(8000),> , Diag Code 3
--,<box214, varchar(8000),> , Diag Code 4
--,<box215, varchar(8000),> , Diag Code 5
--,<box216, varchar(8000),> , Diag Code 6
--,<box217, varchar(8000),> , Diag Code 7
--,<box218, varchar(8000),> , Diag Code 8
--,<box219, varchar(8000),> , Diag Code 9
--,<box2110, varchar(8000),> , Diag Code 10
--,<box2111, varchar(8000),> , Diag Code 11
--,<box2112, varchar(8000),> , Diag Code 12
--A06.CDWWork.FBCS.UB92
--,<diag1, varchar(8000),>
--,<diag2, varchar(8000),>
--,<diag3, varchar(8000),>
--,<diag4, varchar(8000),>
--,<diag5, varchar(8000),>
--,<diag6, varchar(8000),>
--,<diag7, varchar(8000),>
--,<diag8, varchar(8000),>
--,<diag9, varchar(8000),>
--,<diag10, varchar(8000),>
--,<diag11, varchar(8000),>
--,<diag12, varchar(8000),>
--,<diag13, varchar(8000),>
--,<diag14, varchar(8000),>
--,<diag15, varchar(8000),>
--,<diag16, varchar(8000),>
--,<diag17, varchar(8000),>
--,<diag18, varchar(8000),>
--,<diag19, varchar(8000),>
--,<diag20, varchar(8000),>
--,<diag21, varchar(8000),>
--,<diag22, varchar(8000),>
--,<diag23, varchar(8000),>
--,<diag24, varchar(8000),>
--,<diag25, varchar(8000),>
--,<diag1poa, varchar(8000),>
--,<diag2poa, varchar(8000),>
--,<diag3poa, varchar(8000),>
--,<diag4poa, varchar(8000),>
--,<diag5poa, varchar(8000),>
--,<diag6poa, varchar(8000),>
--,<diag7poa, varchar(8000),>
--,<diag8poa, varchar(8000),>
--,<diag9poa, varchar(8000),>
--,<diag10poa, varchar(8000),>
--,<diag11poa, varchar(8000),>
--,<diag12poa, varchar(8000),>
--,<diag13poa, varchar(8000),>
--,<diag14poa, varchar(8000),>
--,<diag15poa, varchar(8000),>
--,<diag16poa, varchar(8000),>
--,<diag17poa, varchar(8000),>
--,<diag18poa, varchar(8000),>
--,<diag19poa, varchar(8000),>
--,<diag20poa, varchar(8000),>
--,<diag21poa, varchar(8000),>
--,<diag22poa, varchar(8000),>
--,<diag23poa, varchar(8000),>
--,<diag24poa, varchar(8000),>
--,<diag25poa, varchar(8000),>
--,<ediag2, varchar(30),> --code for eTrauma2
--,<ediag3, varchar(30),> --code for eTrauma3
--,<patdiagbox70, varchar(30),> --code for Visit70


--fields
FBCSClaimDiagnosisID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
FBCSHCFAClaimID bigint, --EPRS assigned (FK), comes from FBCSHCFAClaims PK
FBCSUBClaimID bigint, --EPRS assigned (FK), comes from FBCSUBClaims PK
DiagnosisCodeID int NOT NULL, --EPRS assigned (FK), comes from DiagnosisCodes
BatchLogID bigint, --EPRS assigned (FK), reference BatchLogs
PositionCode varchar(10), --identifies the position, based on the field name (01-25, eTrauma02, eTrauma03, Visit70)
PresentOnAdmissionCode varchar(5) NULL, --will only be filled in where there are diag##poa fields)

--primary key
CONSTRAINT PK_FBCSClaimDiagnoses PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
FBCSClaimDiagnosisID 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.FBCSClaimDiagnoses
ADD CONSTRAINT FK_FBCSClaimDiagnoses_FBCSHCFAClaims
FOREIGN KEY (FBCSHCFAClaimID)
REFERENCES dbo.FBCSHCFAClaims (FBCSHCFAClaimID)
GO

ALTER TABLE dbo.FBCSClaimDiagnoses
ADD CONSTRAINT FK_FBCSClaimDiagnoses_FBCSUBClaims
FOREIGN KEY (FBCSUBClaimID)
REFERENCES dbo.FBCSUBClaims (FBCSUBClaimID)
GO

ALTER TABLE dbo.FBCSClaimDiagnoses
ADD CONSTRAINT FK_FBCSClaimDiagnoses_DiagnosisCodes
FOREIGN KEY (DiagnosisCodeID)
REFERENCES dbo.DiagnosisCodes (DiagnosisCodeID)
GO

ALTER TABLE dbo.FBCSClaimDiagnoses
ADD CONSTRAINT FK_FBCSClaimDiagnoses_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO

--indexes

/** commenting out due to deploy error
CREATE INDEX statement failed. A clustered columnstore index cannot be created over referencing column 'FK_FBCSClaimDiagnoses_FBCSUBClaims' on table 'FBCSClaimDiagnoses'.
--Create clustered columnstore index that doesn't exist yet
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_FBCSClaimDiagnoses ON dbo.FBCSClaimDiagnoses
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
--SQL 2016-- , 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 IX_FBCSClaimDiagnoses_FBCSHCFAClaimID ON dbo.FBCSClaimDiagnoses
(
FBCSHCFAClaimID
) 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_FBCSClaimDiagnoses_FBCSUBClaimID ON dbo.FBCSClaimDiagnoses
(
FBCSUBClaimID
) 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_FBCSClaimDiagnoses_DiagnosisCodeID ON dbo.FBCSClaimDiagnoses
(
DiagnosisCodeID
) 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

--commenting out below due to error on deployment.
--Msg 35361, Level 16, State 0, Line 63
--CREATE INDEX statement failed. A clustered columnstore index cannot be created over referencing column 'FK_FBCSClaimDiagnoses_FBCSUBClaims' on table 'FBCSClaimDiagnoses'.
/***
CREATE NONCLUSTERED INDEX IX_FBCSClaimDiagnoses_PositionCode ON dbo.FBCSClaimDiagnoses
(
PositionCode
) 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 A06.CDWWork.FBCS.hfca and A06.CDWWork.FBCS.ub92',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSClaimDiagnoses',
N'COLUMN', N'FBCSClaimDiagnosisID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to the FBCS HCFA Claims table',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSClaimDiagnoses',
N'COLUMN', N'FBCSHCFAClaimID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to the FBCS UB Claims table',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSClaimDiagnoses',
N'COLUMN', N'FBCSUBClaimID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Diagnosis Codes table',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSClaimDiagnoses',
N'COLUMN', N'DiagnosisCodeID'
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'FBCSClaimDiagnoses',
N'COLUMN', N'BatchLogID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Position order for the diagnosis code (e.g., Diag Code 01 thru 25, Visit70, eTrauma02, eTrauma03)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSClaimDiagnoses',
N'COLUMN', N'PositionCode'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifies the code if the diagnosis was present on admission',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSClaimDiagnoses',
N'COLUMN', N'PresentOnAdmissionCode'
GO