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.FBCSUBClaimInpatientDRGs
(
--authoritative sources:
--A06.CDWWork.FBCS.UB92
--,<ip_alos, numeric(28,6),>
--,<ip_Approvedfrom, datetime2(7),>
--,<ip_Approvedto, datetime2(7),>
--,<ip_drgnumber, bigint,>
--,<ip_drgprice, numeric(28,6),>
--,<ip_percentdrg, numeric(28,6),>
--,<ip_Disapprovedfrom, datetime2(7),>
--,<ip_Disapprovedto, datetime2(7),>
--,<ip_Disapprovedreasons, varchar(8000),>
--,<ip_priceperday, numeric(28,6),>
--,<ip_numDisapprovedda, numeric(28,6),>
--,<ip_perdiem, numeric(28,6),>
--,<ip_drgweight, numeric(28,6),>
--fields
FBCSUBClaimInpatientDRGID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
FBCSUBClaimID bigint, --EPRS assigned (FK), references FBCSUBClaims
DiagnosisRelatedGroupID smallint NOT NULL, --EPRS assigned (FK), references DiagnosisRelatedGroup
BatchLogID bigint, --ERPS assigned (FK), references BatchLogs
DateApprovedFrom datetime2(7), --ip_Approvedfrom
DateApprovedTo datetime2(7), --ip_Approvedto
DateDisapprovedFrom datetime2(7), --ip_Disapprovedfrom
DateDisapprovedTo datetime2(7), --ip_Disapprovedto
DRGPrice money, --ip_drgprice
PricePerDay money, --ip_priceperday
PerDiem money, --ip_perdiem
DRGPercent numeric(18,3), --ip_percentdrg
DRGWeight numeric(18,3), --ip_drgweight
AverageLengthOfStay numeric(18,3), --ip_alos
NumberDaysDisapproved numeric(18,3), --ip_numDisapprovedda
DisapprovedReason varchar(500), --ip_Disapprovedreasons
--primary key
CONSTRAINT PK_FBCSUBClaimInpatientDRGs PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
FBCSUBClaimInpatientDRGID 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.FBCSUBClaimInpatientDRGs
ADD CONSTRAINT FK_FBCSUBClaimInpatientDRGs_FBCSUBClaims
FOREIGN KEY (FBCSUBClaimID)
REFERENCES dbo.FBCSUBClaims (FBCSUBClaimID)
GO
ALTER TABLE dbo.FBCSUBClaimInpatientDRGs
ADD CONSTRAINT FK_FBCSUBClaimInpatientDRGs_DiagnosisRelatedGroups
FOREIGN KEY (DiagnosisRelatedGroupID)
REFERENCES dbo.DiagnosisRelatedGroups (DiagnosisRelatedGroupID)
GO
ALTER TABLE dbo.FBCSUBClaimInpatientDRGs
ADD CONSTRAINT FK_FBCSUBClaimInpatientDRGs_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO
--indexes
/**
--Create clustered columnstore index that doesn't exist yet
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_FBCSUBClaimInpatientDRGs ON dbo.FBCSUBClaimInpatientDRGs
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_FBCSUBClaimInpatientDRGs_FBCSUBClaimID ON dbo.FBCSUBClaimInpatientDRGs
(
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_FBCSUBClaimInpatientDRGs_DiagnosisRelatedGroupID ON dbo.FBCSUBClaimInpatientDRGs
(
DiagnosisRelatedGroupID
) 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'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'FBCSUBClaimInpatientDRGID'
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'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'FBCSUBClaimID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Diagnosis Related Groups (DRGs) table',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'DiagnosisRelatedGroupID'
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'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'BatchLogID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date Approved from',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'DateApprovedFrom'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date Approved to',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'DateApprovedTo'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date Disapproved from',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'DateDisapprovedFrom'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date Disapproved to',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'DateDisapprovedTo'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Price associated with diagnosis related group',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'DRGPrice'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Price per day',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'PricePerDay'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Per diem rate associated with diagnosis related group (DRG)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'PerDiem'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Percent rate associated with diagnosis related group (DRG)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'DRGPercent'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Weight associated with diagnosis related group (DRG)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'DRGWeight'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Average length of stay associated with diagnosis related group (DRG) for this inpatient stay; unlike Diagnosis Related Groups table, this amount is not always the same for a specific DRG',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'AverageLengthOfStay'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Number of days Disapproved',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'NumberDaysDisapproved'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Reason for disapproval',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimInpatientDRGs',
N'COLUMN', N'DisapprovedReason'
GO