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.FBCSUBClaimABCs
(
--authoritative sources:
--A06.CDWWork.FBCS.UB92
--,<payernamea, varchar(8000),> --TODO: take LEFT(500), position code A
--,<payernameb, varchar(8000),> --TODO: take LEFT(500), position code B
--,<payernamec, varchar(8000),> --TODO: take LEFT(500), position code C
--box60a, box60b, box60c
--healthplanida, healthplanidb, healthplanidc
--relinfoa, relinfob, relinfoc
--otherphysiciannpia, otherphysiciannpib
--otherphysiciannamea, otherphysiciannameb
--asssignmentofbenefitsa, asssignmentofbenefitsb, asssignmentofbenefitsc (not a misspelling)
--priorpaymentsa, priorpaymentsb, priorpaymentsc
--estamountduea, estamountdueb, estamountduec
--insuredsnamea, insuredsnameb, insuredsnamec
--patientsrelationshipa, patientsrelationshipb, patientsrelationshipc
--insureduniqueida, insureduniqueidb, insureduniqueidc
--groupnamea, groupnameb, groupnamec
--insurancegroupnoa, insurancegroupnob, insurancegroupnoc
--otherproccodea, otherproccodeb, otherproccodec
--otherprocdatea, otherprocdateb, otherprocdatec
--fields
FBCSUBClaimABCID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
FBCSUBClaimID bigint NOT NULL, --EPRS assigned (FK), comes from FBCSUBClaims PK
--otherphysiciannpia, otherphysiciannpib, otherphysiciannamea, otherphysiciannameb
OtherPhysicianProviderID bigint, --EPRS assigned (FK), reference Providers
BatchLogID bigint, --EPRS assigned (FK), reference BatchLogs
PositionCode char(1) NOT NULL, --A, B, C, D, E
InsuredIdentificationNumber varchar(50), --box60a, box60b, box60c, TODO: Take LEFT(50) characters
PayerName varchar(500), --payernamea, payernameb, payernamec, take LEFT(500)
HealthPlanNumber varchar(100), --healthplanida, healthplanidb, healthplanidc
ReleaseInformation varchar(100), --relinfoa, relinfob, relinfoc
BenefitsAssignment varchar(10), --asssignmentofbenefitsa, asssignmentofbenefitsb, asssignmentofbenefitsc; TODO: these are mostly Y/N, but some junk, should we convert to bit?
PriorPayment money, --priorpaymentsa, priorpaymentsb, priorpaymentsc
EstimatedAmountDue money, --estamountduea, estamountdueb, estamountduec
InsuredName varchar(500), --insuredsnamea, insuredsnameb, insuredsnamec
InsuredUniqueNumber varchar(10), --insureduniqueida, insureduniqueidb, insureduniqueidc
GroupName varchar(200), --groupnamea, groupnameb, groupnamec
InsuranceGroupNumber varchar(200), --insurancegroupnoa, insurancegroupnob, insurancegroupnoc
PatientRelationship varchar(250), --patientsrelationshipa, patientsrelationshipb, patientsrelationshipc
OtherProcedureCode varchar(10), --otherproccodea, otherproccodeb, otherproccodec, otherproccoded, otherproccodee
OtherProcedureDate datetime2(7), --otherprocdatea, otherprocdateb, otherprocdatec, otherprocdated, otherprocdatee
--primary key
CONSTRAINT PK_FBCSUBClaimABCs PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
FBCSUBClaimABCID 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.FBCSUBClaimABCs
ADD CONSTRAINT FK_FBCSUBClaimABCs_FBCSUBClaims
FOREIGN KEY (FBCSUBClaimID)
REFERENCES dbo.FBCSUBClaims (FBCSUBClaimID)
GO
ALTER TABLE dbo.FBCSUBClaimABCs
ADD CONSTRAINT FK_FBCSUBClaimABCs_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO
--indexes
/**
--CREATE INDEX statement failed. A clustered columnstore index cannot be created over referencing column 'FK_FBCSUBClaimABCs_FBCSUBClaims' on table 'FBCSUBClaimABCs'.
--Create clustered columnstore index that doesn't exist yet
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_FBCSUBClaimABCs ON dbo.FBCSUBClaimABCs
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_FBCSUBClaimABCs_FBCSUBClaimID ON dbo.FBCSUBClaimABCs
(
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_FBCSUBClaimABCs_PositionCode ON dbo.FBCSUBClaimABCs
(
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; authoritative source table is A06.CDWWork.FBCS.ub92',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'FBCSUBClaimABCID'
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'FBCSUBClaimABCs',
N'COLUMN', N'FBCSUBClaimID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: otherphysiciannpi + otherphysicianname (a, b)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'OtherPhysicianProviderID'
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'FBCSUBClaimABCs',
N'COLUMN', N'BatchLogID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Position code (e.g., A, B, C, D, E)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'PositionCode'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Insured identification number, source: box60 (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'InsuredIdentificationNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Payer name, source: payername (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'PayerName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Healthplan number, source: healthplanid (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'HealthPlanNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Release of information, source: relinfo (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'ReleaseInformation'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Assignment of benefits, source: asssignmentofbenefits (sic) (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'BenefitsAssignment'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Prior payments, source: priorpayments (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'PriorPayment'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Estimated amount due, source: estamountdue (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'EstimatedAmountDue'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Insured name, source: insuredsname (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'InsuredName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Insured unique number, source: insureduniqueid (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'InsuredUniqueNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Group name, source: groupname (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'GroupName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Insured group number, source: insurancegroupno (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'InsuranceGroupNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Patient relationship, source: patientsrelationship (a-c)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'PatientRelationship'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Other procedure code, source: otherproccode (a - e)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'OtherProcedureCode'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Other procedure date, source: otherproccode (a - e)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimABCs',
N'COLUMN', N'OtherProcedureDate'
GO