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.FBCSUnprocessedClaims
(
--authoritative source:
--A06.CDWWork.FBCS.claimsnotprocessed
--fields
FBCSUnprocessedClaimID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
PatientID bigint, --EPRS assigned (FK), reference Patients
StationID smallint NOT NULL, --EPRS assigned (FK), references Stations, trade in station__no + facility
ClaimTypeID tinyint, --EPRS assigned (FK), reference ClaimTypes, claim_type
CallCenterProgramID tinyint, --EPRS assigned (FK), references CallCenterPrograms
ClaimAgeGroupID tinyint, --EPRS assigned (FK), references ClaimAgeGroups, ageby30dayinc
SuspensionReasonID smallint, --EPRS assigned (FK), references Reasons, suspension_reason
BillingProviderID bigint, --EPRS assigned (FK), references Providers, billing_provider
BatchLogID bigint, --EPRS assigned (FK), reference BatchLogs
FBCSClaimNumber bigint, --claim_id, TODO: Is this going to be a future Claims table? otherwise, rename as FBCSClaimNumber
FederalTaxNumber varchar(20),
FPPSNumber bigint, --Federal Personal Payroll System ID?
DateOfService datetime2(7),
DateScanned datetime2(7),
DateReopened datetime2(7),
DateSuspended datetime2(7),
--BillingProviderName varchar(200), --TODO: See if can use the FederalTaxNumber to relate to a specific provider in HCFA/UB provider
TotalCharges money,
SpecialProvisionCategory varchar(50),
--primary key
CONSTRAINT PK_FBCSUnprocessedClaims PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
FBCSUnprocessedClaimID ASC
)
)
ON CoreData
--when using clustered columnstore index, can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE)
GO
/*
station__no VARCHAR(30) NOT NULL, --StationID
claim_type VARCHAR(4), --[migrate to ClaimTypes, ClaimTypeID]
claim_id BIGINT, --FBCSClaimNumber
fpps_id VARCHAR(255),
billing_provider VARCHAR(8000),
from_dos VARCHAR(152),
program VARCHAR(12),
date_scanned DATETIME2(7),
reopened_date DATETIME2(7),
total_charges NUMERIC(28, 6),
[status] VARCHAR(13),
ageby30dayinc VARCHAR(18), --
facility VARCHAR(8000),
tax_id VARCHAR(20),
suspension_reason VARCHAR(200),
suspended_date DATETIME,
specialprovcat VARCHAR(50),
patientien VARCHAR(50),
thread__id NUMERIC(18,0) NOT NULL,
*/
--foreign keys
--TODO: Determine if there will be a claims table. If so, uncomment the following. Otherwise, assume no FK need on ClaimID
--ALTER TABLE dbo.FBCSUnprocessedClaims
-- ADD CONSTRAINT FK_FBCSUnprocessedClaims_ClaimID
-- FOREIGN KEY (ClaimID)
-- REFERENCES dbo.Claims (ClaimID)
--GO
/*
ALTER TABLE dbo.FBCSUnprocessedClaims
ADD CONSTRAINT FK_FBCSUnprocessedClaims_Patients
FOREIGN KEY (PatientID)
REFERENCES dbo.Patients (PatientID)
GO
*/
ALTER TABLE dbo.FBCSUnprocessedClaims
ADD CONSTRAINT FK_FBCSUnprocessedClaims_ClaimTypes
FOREIGN KEY (ClaimTypeID)
REFERENCES dbo.ClaimTypes (ClaimTypeID)
GO
ALTER TABLE dbo.FBCSUnprocessedClaims
ADD CONSTRAINT FK_FBCSUnprocessedClaims_Stations
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO
ALTER TABLE dbo.FBCSUnprocessedClaims
ADD CONSTRAINT FK_FBCSUnprocessedClaims_CallCenterPrograms
FOREIGN KEY (CallCenterProgramID)
REFERENCES dbo.CallCenterPrograms (CallCenterProgramID)
GO
ALTER TABLE dbo.FBCSUnprocessedClaims
ADD CONSTRAINT FK_FBCSUnprocessedClaims_Providers
FOREIGN KEY (BillingProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO
ALTER TABLE dbo.FBCSUnprocessedClaims
ADD CONSTRAINT FK_FBCSUnprocessedClaims_ClaimAgeGroups
FOREIGN KEY (ClaimAgeGroupID)
REFERENCES dbo.ClaimAgeGroups (ClaimAgeGroupID)
GO
ALTER TABLE dbo.FBCSUnprocessedClaims
ADD CONSTRAINT FK_FBCSUnprocessedClaims_SuspensionReasons
FOREIGN KEY (SuspensionReasonID)
REFERENCES dbo.Reasons (ReasonID)
GO
ALTER TABLE dbo.FBCSUnprocessedClaims
ADD CONSTRAINT FK_FBCSUnprocessedClaims_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO
--indexes
/**
--Create clustered columnstore index that doesn't exist yet
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_FBCSUnprocessedClaims ON dbo.FBCSUnprocessedClaims
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_FBCSUnprocessedClaims_PatientID ON dbo.FBCSUnprocessedClaims
(
PatientID
) 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_FBCSUnprocessedClaims_ClaimTypeID ON dbo.FBCSUnprocessedClaims
(
ClaimTypeID
) 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_FBCSUnprocessedClaims_StationID ON dbo.FBCSUnprocessedClaims
(
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 --use page compression; default = NONE
)
ON CoreIndex
GO
CREATE NONCLUSTERED INDEX IX_FBCSUnprocessedClaims_DateOfService ON dbo.FBCSUnprocessedClaims
(
DateOfService
) 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.claimsnotprocessed',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'FBCSUnprocessedClaimID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Patients table, source field: PatientIEN',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'PatientID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Claim Types table, source field: claim_type',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'ClaimTypeID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Stations table, source field: station__no + facility',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'StationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Call Center Programs table, source field: program',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'CallCenterProgramID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Claim Age Groups table, source field: ageby30dayinc',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'ClaimAgeGroupID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Reasons table, source field: suspension_reason',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'SuspensionReasonID'
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'FBCSUnprocessedClaims',
N'COLUMN', N'BatchLogID'
GO
--TODO: Revisit the description on ClaimID if we have a Claims table in future
EXECUTE sp_addextendedproperty
N'MS_Description', N'Represents the ClaimID from FBCS, if we have a Claims table, it would relate to Claims table, presently unrelated, source field: claim_id',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'FBCSClaimNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Federal Tax ID, source field: tax_id',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'FederalTaxNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Federal Personal Payroll System ID?, source field: fpps_id',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'FPPSNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date service was provided, source field: from_dos',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'DateOfService'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date claim was scanned, source field: date_scanned',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'DateScanned'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date claim was reopened, source field: reopened_date',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'DateReopened'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date claim was suspended, source field: suspended_date',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'DateSuspended'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Total charges for claim, source field: total_charges',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'TotalCharges'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Special provision category, source field: specialprovcat',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUnprocessedClaims',
N'COLUMN', N'SpecialProvisionCategory'
GO