The utilizationperiod table creates a time-based data structure to represent many concepts in the data model in a simple construct that readily supports trends or fast summarizations for arbitrary time periods. Each row represents a month in time for a given person. Columns include: aggregates of common utilization elements (i.e. counts of hospitalizations or ED, total cost, etc.) and person features (i.e. attribution, eligibility, etc.) for that month in time.
Usage
This table structure readily enables trending and is commonly filtered by begindate or enddate (to trend or summarize a given period) or person features calculated for a given month. It can readily be joined to person, derivedperson, or other person* tables in order to compare historical person attributes (in the utilizationperiod table) with current attributes as represented in person* tables.
The utilizationperiod table is particularly useful to produce interactive reports or analyses that compute “per thousand persons” metrics such as “ED visits per thousand members per month” or “per member per month cost” (or annualized versions of these metrics). A count of unique personids for filtered utilizationperiods provides the necessary information to compute the denominator of eligible personids for this pattern.
Alternatively, if the columns in the utilizationperiod table are too restrictive, using underlying tables such as derivedobservation_*, claim, or encounter can provide more expressive data structures to compute trends.
Source Concepts
Table Schema
| Name |
Type
|
Description |
| personid |
VARCHAR(38) GUID |
Unique identifier representing a unique person that may include data from several sources that have been linked. This identifier is the person_id of a HIEBus recordgroup.
|
| startdate |
TIMESTAMP |
The first date of the month summarized by the utilizationperiod. For example, 1/1/2021.
|
| enddate |
TIMESTAMP |
The last date of the month summarized by the utilizationperiod. For example, 1/31/2021.
|
| period |
INTEGER |
The difference in months between the enddate and the most recent utilizationperiod. The most recent utilizationperiod will be indicated by period=0. The previous month will be indicated by period=1; two months prior will be indicated by period=2, etc.
|
| hospitalizations |
INTEGER |
Computed from derivedencounter_inpatient. The number of hospitalizations with an admitdate during the specified utilization period.
|
| hospitalizationsacsc |
INTEGER |
Computed from derivedencounter_inpatient. The number of hospitalizations with an Ambulatory Care Sensitive Condition primary diagnosis (derivedencoutner_inpatient.isacsc=True) and an admitdate during the specified utilization period.
|
| hospitalizationsemergent |
INTEGER |
Computed from derivedencounter_inpatient. The number of hospitalizations admitted via the ED (derivedencoutner_inpatient.isemergent=True) and an admitdate during the specified utilization period.
|
| hospitalizationsbehavioralhealth |
INTEGER |
Computed from derivedencounter_inpatient. The number of hospitalizations with a behavioral health related primary diagnosis (derivedencoutner_inpatient.isbehavioralhealth=True) and an admitdate during the specified utilization period.
|
| hospitalizationsreadmit30d |
INTEGER |
Computed from derivedencounter_inpatient. The number of hospitalizations with a prior hospital discharge in the previous 30 days (with derivedencoutner_inpatient.isreadmit30d=True) and an admitdate during the specified utilization period.
|
| edvisits |
INTEGER |
Computed from derivedencounter_ed. The number of treat and release ED visits with an admitdate during the specified utilization period.
|
| edvisitsavoidable |
INTEGER |
Computed from derivedencounter_ed. The number of treat and release ED visits with an primary diagnosis designated as LANE-MT (Low acuity, non-emergent or minor trauma, indicated by derivedencoutner_ed.isavoidable=True) and an admitdate during the specified utilization period.
|
| edvisitsbehavioralhealth |
INTEGER |
Computed from derivedencounter_ed. The number of treat and release ED visits with an behavioral health related primary diagnosis (derivedencoutner_ed.isbehavioralhealth=True) and an admitdate during the specified utilization period.
|
| edvisitsrepeat30d |
INTEGER |
Computed from derivedencounter_ed. The number of treat and release ED visits with a prior ED discharge in the previous 30 days (derivedencoutner_ed.isrepeat30d=True) and an admitdate during the specified utilization period.
|
| total |
DECIMAL |
The sum of the total amount for all non-pharmacy claims with a startdate during the specified utilization period.
|
| insuranceplan |
VARCHAR |
The primary insurance plan for the person on the startdate of the utilization period.
|
| insuranceplanstartdate |
TIMESTAMP |
The startdate for the insurance plan indicated by the insuranceplan column.
|
| activeclaims |
INTEGER |
|
| amountprior12months |
DECIMAL |
|
| amountnext12months |
DECIMAL |
|
| amountmscannualized |
DECIMAL |
|
| amountmscannualizedadj |
DECIMAL |
|
| attributedaco |
VARCHAR |
|
| attributedprovider |
VARCHAR |
|
| attributionstatus |
VARCHAR |
|
| caremanagementstatus |
VARCHAR |
|
| medicarestatuscode |
VARCHAR |
|
| censusdayshomehealth |
INTEGER |
|
| censusdaysinpatient |
INTEGER |
|
| censusdayssnf |
INTEGER |
|
| enrollmentmonthsprior12months |
INTEGER |
|
| enrollmentmonthsnext12months |
INTEGER |
|
| hccrapsscore |
DECIMAL |
|
| hccunmanagedscore |
DECIMAL |
|
| personage |
INTEGER |
|
| edvisitsprior12months |
INTEGER |
|
| edvisitsnext12months |
INTEGER |
|
| hospitalizationsprior12months |
INTEGER |
|
| hospitalizationsnext12months |
INTEGER |
|
| snfvisits |
INTEGER |
|
| readmissions |
INTEGER |
|
| readmissionsprior12months |
INTEGER |
|
| readmissionsnext12months |
INTEGER |
|
Back to Tables
