documentation:next_cdm:add_person_to_cost
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
documentation:next_cdm:add_person_to_cost [2017/03/12 20:18] gowtham_rao |
documentation:next_cdm:add_person_to_cost [2017/07/10 16:43] (current) clairblacketer |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Add Person_id and (financial) Dates to Cost Table ====== | + | ====== Cost Table Changes (Add Person_id, Dates and normalize) ====== |
| + | ===Proposals are now being tracked as github issues=== | ||
| + | [[https://github.com/OHDSI/CommonDataModel/issues/81|link to github issue]] | ||
| **Proposal Owner:** Gowtham Rao, Chris Knoll, Klaus Bonadt | **Proposal Owner:** Gowtham Rao, Chris Knoll, Klaus Bonadt | ||
| Line 19: | Line 20: | ||
| - | **2. Add billed_datetime, paid_datetime:** | + | **2. Add incurred_date, incurred_datetime, billed_datetime, paid_datetime:** |
| - | * Add two new datetime fields in the cost table. billed_datetime and paid_datetime | + | * Add three new datetime fields in the cost table. billed_datetime and paid_datetime |
| - | * This is in-line with the design principle. | + | * Incurred_date or service_date (match event table) - This is in-line with the design principle. |
| * Both fields are optional (Required = No), they are date time fields (Type = DateTime - following OMOP conventions) | * Both fields are optional (Required = No), they are date time fields (Type = DateTime - following OMOP conventions) | ||
| * Costs are associated with a visit_occurrence, procedure_occurrence, drug_occurrence, observation, device etc. | * Costs are associated with a visit_occurrence, procedure_occurrence, drug_occurrence, observation, device etc. | ||
| Line 49: | Line 50: | ||
| - | **3. Change structure of Cost table from current de-normalized (for cost variables) to normalized (for cost variables)** | + | **3. Leverage cost_type_concept_id and remove cost type columns ** |
| - | Proposed structure of new table. | + | * Proposed structure of new table is below |
| + | * The key idea here is that instead of making each cost-type a column (i.e. wide representation), lets convert to long representation. We will leverage cost_type_concept_id for this. | ||
| + | * cost_type_concept_id will be used to identify standard cost types: total_charge, total_cost, total_paid, paid_by_payer, paid_by_patient, paid_patient_coinsurance, paid_patient_deductible, paid_by_primary, paid_ingredient_cost, paid_dispensing_fee, payer_plan_period_id, amount_allowed. This allows to generalize the cost table and we represent arbitrary number of cost types. We can also represent international and custom use cases. | ||
| + | * Should cost_domain_id be an integer or string? Currently it is a string. | ||
| - | ^ Field ^ Required | Type | Description | | + | ^ Field ^ Required | Type | Description | |
| - | | cost_id | Yes | integer | A unique identifier for each COST record. | | + | | cost_id | Yes | integer | A unique identifier for each COST record. | |
| - | | person_id | Yes | integer | A unique identifier for each person. | | + | | person_id | Yes | integer | A unique identifier for each person. | |
| - | | cost_event_id | Yes | integer | A foreign key identifier to the event (e.g. Measurement, Procedure, Visit, Drug Exposure, etc) record for which cost data are recorded. | | + | | cost_event_id | Yes | integer | A foreign key identifier to the event (e.g. Measurement, Procedure, Visit, Drug Exposure, etc) record for which cost data are recorded. | |
| - | | cost_domain_id | Yes | integer | The concept representing the domain of the cost event, from which the corresponding table can be inferred that contains the entity for which cost information is recorded. | | + | | cost_domain_id | Yes | integer | The concept id representing the domain of the cost event, from which the corresponding table can be inferred that contains the entity for which cost information is recorded. | |
| - | | cost_type_concept_id | Yes | integer | A foreign key identifier to a concept in the CONCEPT table for the provenance or the source of the COST data: Calculated from insurance claim information, provider revenue, calculated from cost-to-charge ratio, reported from accounting database, etc. | | + | | cost_type_concept_id | Yes | integer | A foreign key identifier to a concept in the CONCEPT table for the provenance or the source of the COST data: Calculated from insurance claim information, provider revenue, calculated from cost-to-charge ratio, reported from accounting database, etc. | |
| - | | currency_concept_id | Yes | integer | A foreign key identifier to the concept representing the 3-letter code used to delineate international currencies, such as USD for US Dollar. | | + | | currency_concept_id | Yes | integer | A foreign key identifier to the concept representing the 3-letter code used to delineate international currencies, such as USD for US Dollar. | |
| - | | cost_amount_type_concept_id | Yes | integer | cost_amount_type_concept_id to represent total_charge, total_cost, total_paid, paid_by_payer, paid_by_patient, paid_patient_coinsurance, paid_patient_deductible, paid_by_primary, paid_ingredient_cost, paid_dispensing_fee, payer_plan_period_id, amount_allowed. This allows to generalize the cost table and we represent arbitrary number of cost types. We can also represent international and custom use cases.cost_amount_type_concept_id to represent total_charge, total_cost, total_paid, paid_by_payer, paid_by_patient, paid_patient_coinsurance, paid_patient_deductible, paid_by_primary, paid_ingredient_cost, paid_dispensing_fee, payer_plan_period_id, amount_allowed. This allows to generalize the cost table and we represent arbitrary number of cost types. We can also represent international and custom use cases. | | + | | total_cost | Yes | float | The actual financial cost amount | |
| - | | cost_amount | Yes | float | This allows to generalize the cost table and we represent arbitrary number of cost types. We can also represent international and custom use cases.The actual financial transaction amount | | + | | incurred_date | Yes | date | The first date of service of the clinical event as in table capturing the information (e.g. date of visit, date of procedure, date of condition, date of drug etc). | |
| - | | billed_datetime | No | datetime | The date and time a bill was generated for a service or encounter | | + | | incurred_datetime | No | datetime | | |
| - | | paid_datetime | No | datetime | The date and time payment was received for a service or encounter | | + | | billed_datetime | No | datetime | The date and time a bill was generated for a service or encounter | |
| - | | revenue_code_concept_id | No | integer | A foreign key referring to a Standard Concept ID in the Standardized Vocabularies for Revenue codes. | | + | | paid_datetime | No | datetime | The date and time payment was received for a service or encounter | |
| - | | drg_concept_id | No | integer | A foreign key referring to a Standard Concept ID in the Standardized Vocabularies for DRG codes. | | + | | revenue_code_concept_id | No | integer | A foreign key referring to a Standard Concept ID in the Standardized Vocabularies for Revenue codes. | |
| - | | revenue_code_source_value | No | string(50) | The source code for the Revenue code as it appears in the source data, stored here for reference. | | + | | drg_concept_id | No | integer | A foreign key referring to a Standard Concept ID in the Standardized Vocabularies for DRG codes. | |
| - | | drg_source_value | No | string(50) | The source code for the 3-digit DRG source code as it appears in the source data, stored here for reference. | | + | | revenue_code_source_value | No | string(50) | The source code for the Revenue code as it appears in the source data, stored here for reference. | |
| + | | drg_source_value | No | string(50) | The source code for the 3-digit DRG source code as it appears in the source data, stored here for reference. | | ||
documentation/next_cdm/add_person_to_cost.1489349883.txt.gz · Last modified: 2017/03/12 20:18 by gowtham_rao
Page Tools
