Integrated Veteran Care Consolidated Data Sets (IVC CDS) Cost Data

The VHA Office of Integrated Veteran Care Consolidated Data Sets (IVC CDS) is the primary source for VA community care data. IVC CDS contains referrals, claims, and eventually payment data from Community Care Reimbursement System (CCRS), Electronic Claims Adjudication Management System (eCAMS), Fee Basis Claims System (FBCS), Plexis Claims Manager (PCM), and VistA Fee Basis Package (Fee). Data are available for claims processed on or after October 1, 2018.

On this page, we provide an overview of cost data in CDS, and we present one method for identifying the cost of an inpatient hospital stay using CDS data. Investigations into outpatient cost data are underway.

Learn more:

• IVC CDS Data Review from VIReC (VA intranet only): https://vaww.virec.research.va.gov/Reports/DR/DR-IVC-CDS.pdf
• IVC CDS Data Dictionary from VIReC (Available on the VHA Data Portal; VA intranet only): https://vaww.vhadataportal.med.va.gov/Data-Sources/IVC-Consolidated-Data-Sets
• IVC Consolidated Data Sets on the VHA Data Portal (VA intranet only): https://vaww.vhadataportal.med.va.gov/Data-Sources/IVC-Consolidated-Data-Sets
• IVC CDS Data Review: Referrals from VIReC (VA intranet only): https://vaww.virec.research.va.gov/Reports/DR/DR-IVC-CDS-Referrals.pdf
• Finding Hospital Inpatient Stays in CDS from ACCENT
• Information on Specific Community Care Programs and Policies from ACCENT includes useful information on IVC CDS data including SQL scripts and cyberseminars.
• A Brief Introduction to a New Community Care Data Source: IVC_CDS (VA intranet only): https://vincicentral.vinci.med.va.gov/Shared%20Documents/Videos/A_Brief_Introduction_to_a_New%20Community_Care_Data_Source-IVC_CDS-092023.mp4

Cost Data Overview

Community care data include claims-level and other information about care submitted for reimbursement by a non-VA facility. All claims will have a header record and associated line detail records. The header record contains the total amount VA paid on the claim while the line records contain payments for specific services.

The granularity you need will depend on your research question, but for most research questions, we recommend using header level data. HERC researchers have found that payments at the header and line levels match in approximately 98% of claims, and the agreement between header and line level claim payments has increased over time. We’ve found that a large portion of the mismatched header and line claim payments are sourced from eCAMS and PCM and suggest that data users look out for claims from eCAMS with $0 payments at the line level, but payments greater than $0 at the header level.

Claims can be submitted using three different forms: institutional, professional, and dental. Institutional claims can be for inpatient or outpatient care and are typically submitted by hospitals and other healthcare organizations. Professional claims can also be for care delivered in an outpatient, inpatient, or other setting and are submitted by physicians and other healthcare providers. The information included in a claim varies depending on the type of claim form.

Many inpatient and outpatient services contain both an institutional component and a professional component, and researchers will need to sum these to calculate the total cost of the service. In addition, researchers interested in inpatient care specifically should be aware that multiple claims may need to be aggregated to capture the total cost of the inpatient stay.

return to top

Key Variables for Cost Analyses

Below are key variables for conducting cost analyses using CDS data. A full list of variables is available in the IVC CDS Data Dictionary, available on the VHA Data Portal (VA intranet only: https://vaww.vhadataportal.med.va.gov/Data-Sources/IVC-Consolidated-Data-Sets). 

*Claims may be resubmitted (see Duplicate Claims). Therefore, for many inquiries, researchers will need to identify the most recent submission of a claim.

return to top

Duplicate Claims

Data users may find duplicate claims in the data due to resubmission of claims and claims from multiple source systems. Limiting to records for a unique patient admission where Status_Description in (‘PAID’) and IsCurrent not in (‘R’,’D’) (reversed or deleted) will eliminate many duplicate claims. However, you may still find duplicates after applying these filters. Therefore, in addition to the above filters, data users should look for and remove duplicate claims.

Within inpatient data, one method for removing duplicates within a source system is by using header level Patient_ICN + Original_ClaimID, and between source systems by using header level Patient_ICN  + minimum of Service_Start_Date + maximum of Service_End_date and Bill_Type (institutional records) or Place_of_Service_ID (professional records), for claims with the same Claim_Total_Amount (see the section “Identifying the Cost of an Inpatient Hospital Stay“). The maximum and minimum service dates are aggregated across contiguous claims to form inpatient stays and serve as the admit and discharge dates.  (We do not use the Admission_Date and Discharge_Date fields from CDS because of missingness.)

Users should always de-duplicate when the same claim appears in both FBCS and VistA (Fee) source systems; we recommend keeping claims from FBCS since they're generally more complete. Users may also encounter claims that appear to be duplicates originating from other systems or within the same system. We offer suggested solutions within our code to deal with these types of duplicates, but we leave the final decision to de-duplicate these claims to the individual researcher as we’re unable to definitively determine if duplicate sets of claims were actually paid (perhaps erroneously).

The number of duplicate claims are greatest in 2019 data and decrease over the years. While HERC analysts examining line level claims have identified approximately 30% of claims as potential duplicates in FY19, this drops to 5% in FY20, 0.07% in FY21, and <0.01% in FY22 and onwards. The large number of duplicates in FY19 is primarily due to duplication between FBCS and VistA (Fee).  

Learn more about the reasons for duplicate claims in VIReC’s IVC CDS Data Review (VA intranet only: https://vaww.virec.research.va.gov/Reports/DR/DR-IVC-CDS.pdf).

return to top

Time to Claim Processing

Data users should allow enough time for all claims to be processed before pulling cost data. IVC recommends allowing at least a 4-month processing time from the last date of service before including claims in a cost analysis. 

return to top

Identifying the Total Cost of an Inpatient Stay

In this section, we share an algorithm for identifying the cost of an inpatient hospital stay and sample SQL code. This is only one method for identifying inpatient costs and is meant to serve as a starting point for data users conducting their own evaluations.

This method uses the CDS_Claim_Header file. We use Billing_Provider_TaxID as the facility identifier; therefore, without de-duplicating, this method may lead to overcounting of stays. Data users should de-duplicate to address potential overcounting.

Steps for Identifying the Total Cost of an Inpatient Stay

• Allow for sufficient claim processing lead time, e.g., 4 months.

1. Identify the Institutional Component of the Stay

• Filter header records by Claim_Form_Type = ’I’ (institutional), IsCurrent not in (‘R’,’D’) (reversed or deleted), Bill_Type 11x, 12x, 41x, 42x, 44x*, and Claim_Status_ID = 71 (PAID).
• Create a new variable: AdmitID. Define AdmitID as unique admission: Patient_ICN + Billing_Provider_TaxID + [Contiguous Service Dates].
• With unique AdmitID, calculate Service_Start_Date_Min and Service_End_Date_Max (Minimum of Service_Start_Date and Maximum of Service_End_Date).
• De-duplicate claims with the same Claim_Total_Amount and within the same source system using Patient_ICN + Original_ClaimID.
• De-duplicate FBCS and VISTA claims with the same Claim_Total_Amount using Patient_ICN + Service_Start_Date_Min + Service_End_Date_Max + Bill_Type
• With AdmitID, roll-up Claim_Total_Amount by Bill_Type x Source_System.
• For each AdmitID, sum Bill_Type x Source_System … this is the Institutional Component.

2. Identify the Professional Component of the Stay

• Within AdmitID, roll-up Claim_Total_Amount by Place_of_Service_ID x Source_System x Service_Start_Date with Claim_Status_ID = 71 (PAID) and IsCurrent not in (‘R’,’D’) (reversed or deleted) and Claim_Form_Type = P (Professional) and Place_of_Service_ID = 21**
• Link Patient_ICN + Service_Start_Date to the get the AdmitID (SQL join with Patient_ICN + Service_Start_Date_Min + Service_End_Date_Max)
• De-duplicate claims with the same Claim_Total_Amount and within the same source system using Patient_ICN + Original_ClaimID.
• De-duplicate FBCS and VISTA claims with the same Claim_Total_Amount using Patient_ICN + Service_Start_Date_Min + Service_End_Date_Max + Place_of_Service_ID
• Collapse Claim_Total_Amount by AdmitID x Source_System … select the sum as the Professional Component.

3. Calculate Total Costs

• Match the inpatient component to the professional component using Service_Start_Date_Min + Service_End_Date_Max
• Sum Claim_Total_Amount for the Institutional Component + Professional Component to calculate Hospital Stay Total Cost.

*The bill types researchers include may vary depending on their research question. A full list of bill type codes is available from ResDAC.

**Researchers may choose to add additional place of service codes such as ambulance or psychiatric residential treatment center depending on their study goals. A full list of place of service codes is available on the CMS website. 

return to top

SQL Code for Identifying the Total Costs of an Inpatient Stay

Sample SQL code for identifying the total costs of an inpatient stay is available within VA's Enterprise GitHub: HERC SOP Cost Inpatient Hospital Stay and included here in Appendix A. The code is based on the steps outlined above. This is only one method and is meant to serve as a starting point for data users conducting their own evaluations.

We encourage VA data users to access the SQL code via GitHub for the most current version. 

return to top

Acknowledgements

HERC would like to acknowledge Erin Beilstein-wedel from Access and Community Care Engagement Network Team (ACCENT) for her contributions and review of the contents of this page.

return to top

Appendix A. SQL Code: Total Costs of an Inpatient Stay

 

USE []

GO

 

-- ==========================================================================================

-- Program: HERC-sop-cost-inpatient-hospital-stay.sql

-- Description: Calculate cost of inpatient hospital stay

-- Project: HERC SOP

-- Authors: Vilija Joyce, Adam Chow, Sharon Dally, Juliette Hong, Samantha Illarmo

-- Description: Identify cost of inpatient hospital stay from community care files (CDS) 

-- Create date: 20240510

-- Modified:

-- ==========================================================================================

/*Create temporary cohort table*/

/*Cohort table should include Patient_ICN, Window_Start_Date, Window_End_Date*/

 

/*Pull cohort's CDS claims that fall within window start and end dates*/

drop table if exists #ipcost1;

select l.patient_icn

,l.window_start_date

,l.window_end_date

,concat(l.patient_icn,'_',r1.billing_provider_taxid) as admitid

,r1.service_start_date

,r1.service_end_date

,r1.admission_date

,r1.admission_hour

,r1.discharge_date

,r1.discharge_hour

,r1.original_claimid

,r1.claimid

,r1.claimsid

,r1.source_system

,r1.source_key

,r1.iscurrent

,r1.claim_form_type

,r1.claim_status_id

,r1.place_of_service_id

,r1.bill_type

,r1.referral_number

,r1.station_number

,r1.primary_icd

,r1.admitting_icd

,r1.amount_allowed

,r1.total_charges

,r1.claim_total_amount

,r1.received_date   

,r1.paid_date

,r1.newborn_flag

,r1.billing_provider_npi

,r1.service_provider_npi

,r1.billing_provider_taxid

into #ipcost1

from #cohort l

left outer join [vhacdwa06.vha.med.va.gov].[cdwwork].[ivc_cds].[cds_claim_header] r1 

on (l.patient_icn=r1.patient_icn

and ((r1.service_start_date

and (r1.service_end_date>=l.window_start_date)));

--n=

 

--Ck

select top 100 * from #ipcost1 order by admitid, service_start_date, service_end_date; 

 

--Pull inpatient-related institutional records of interest using claim_form_type, bill_type, claim_status_id, and iscurrent

drop table if exists #ipcost1i;

select * into #ipcost1i from #ipcost1 where (claim_form_type in ('I') and ((bill_type like '1[12]%') or (bill_type like '4[124]%')) and claim_status_id in ('71') and iscurrent not in ('R','D')) order by admitid, service_start_date, service_end_date; 

--n=

 

--Ck

select top 100 * from #ipcost1i order by admitid, service_start_date, service_end_date; 

 

--Pull inpatient-related professional records of interest using claim_form_type, place_of_service_id, claim_status_id, and iscurrent

drop table if exists #ipcost1p;

select * into #ipcost1p from #ipcost1 where (claim_form_type in ('P') and place_of_service_id in ('21') and claim_status_id in ('71') and iscurrent not in ('R','D')) order by admitid, service_start_date, service_end_date; 

--n=

 

--Ck

select top 100 * from #ipcost1p order by admitid, service_start_date, service_end_date; 

 

/*Identify minimum start date and maximum end date for each admitid w/ contiguous service dates*/

drop table if exists #ipcost2i;

select t.admitid 

,min(t.service_start_date) as min_service_start_date

,max(t.service_end_date) as max_service_end_date

into #ipcost2i

from (select t.*

,sum(case when prev_service_end_date >= dateadd(day, -1, t.service_start_date) then 0 else 1 end) over (partition by t.admitid order by t.service_start_date) as grp from (select t.*, lag(t.service_end_date) over (partition by t.admitid order by t.service_start_date) as prev_service_end_date

from #ipcost1i t 

) t 

) t 

group by t.admitid, t.grp;

--n=

 

--Ck

select top 100 * from #ipcost2i order by admitid, min_service_start_date, max_service_end_date; 

 

--Merge back

drop table if exists #ipcost3i;

select l.min_service_start_date

,l.max_service_end_date

,r.*

into #ipcost3i

from #ipcost2i l 

left outer join #ipcost1i r 

on l.admitid=r.admitid and r.service_end_date>=l.min_service_start_date and r.service_start_date<=l.max_service_end_date;

--n=

 

--Ck

select top 100 * from #ipcost3i order by admitid, service_start_date, service_end_date, claim_total_amount, source_system;

 

/*Identify and resolve possible duplicates within source system. If multiple rows have same original_claimid then, calculate min, mean, max, and sum of claim_total_amount. Researcher can decide which value to use moving forward. For now, we use max_cta.

*/

drop table if exists #ipcost3i_;

select patient_icn, original_claimid, min(claim_total_amount) as min_cta, avg(claim_total_amount) as avg_cta, max(claim_total_amount) as max_cta, sum(claim_total_amount) as sum_cta

into #ipcost3i_

from #ipcost3i

group by patient_icn, original_claimid;

--n=

 

--Ck

select top 100 * from #ipcost3i_ order by patient_icn;

 

--Merge back and collapse

drop table if exists #ipcost3i__;

select distinct l.min_service_start_date, l.max_service_end_date, l.patient_icn, l.window_start_date, l.window_end_date, l.admitid, l.service_start_date, l.service_end_date, l.admission_date, l.admission_hour, l.discharge_date, l.discharge_hour, l.original_claimid, l.claimid, l.claimsid, l.source_system, l.source_key, l.iscurrent, l.claim_form_type, l.claim_status_id, l.place_of_service_id, l.bill_type, l.referral_number, l.station_number, l.primary_icd, l.admitting_icd, l.amount_allowed, l.total_charges, r.max_cta as claim_total_amount, l.received_date, l.paid_date, l.newborn_flag, l.billing_provider_npi, l.service_provider_npi, l.billing_provider_taxid

into #ipcost3i__

from #ipcost3i l 

left outer join #ipcost3i_ r 

on l.patient_icn=r.patient_icn and l.original_claimid=r.original_claimid;

--n=

 

--Ck

select top 100 * from #ipcost3i__ order by patient_icn;

 

/*De-duplicate across source systems. Dedupe if rows have same 

- min_service_start_date

- max_service_end_date

- bill_type (first 2 characters)

- claim_total_amount

 

- Flag records that indicate FBCS followed by VISTA 

- Flag records that indicate VISTA followed by VISTA

- Modify code below if you'd like to dedupe by other combinations of source systems, e.g. FBCS and eCams

*/

--Flag records

drop table if exists #ipcost4i;

select *

--, case when lag(original_claimid) over (partition by admitid, original_claimid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, original_claimid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = original_claimid then 1 else 0 end as flag_prev_ocsid_match

, case when lag(min_service_start_date) over (partition by admitid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = min_service_start_date then 1 else 0 end as flag_prev_mssd_match

, case when lag(max_service_end_date) over (partition by admitid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = max_service_end_date then 1 else 0 end as flag_prev_msed_match

, case when lag(substring(bill_type, 1, 2)) over (partition by admitid,  min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = substring(bill_type, 1, 2) then 1 else 0 end as flag_prev_bt_match

, case when lag(source_system) over (partition by admitid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = 'FBCS' and source_system='VISTA' then 1 else 0 end as flag_prev_ss_fbcs_vista

, case when lag(source_system) over (partition by admitid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = 'VISTA' and source_system='VISTA' then 1 else 0 end as flag_prev_ss_vista_vista

, case when lag(claim_total_amount) over (partition by admitid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = claim_total_amount then 1 else 0 end as flag_prev_cta_match

into #ipcost4i

from #ipcost3i__ 

;

--n=

 

--Ck

select top 100 * from #ipcost4i; 

 

--De-duplicate by keeping only those records w/ flags (0,0,0,0,0,0) or (1,1,1,0,0,1) aka drop dupe VISTA records

drop table if exists #ipcost5i;

select *

into #ipcost5i 

from #ipcost4i

where ((flag_prev_mssd_match = 0 and flag_prev_msed_match = 0 and flag_prev_bt_match = 0 and flag_prev_ss_fbcs_vista = 0 and flag_prev_ss_vista_vista = 0 and flag_prev_cta_match = 0)

or (flag_prev_mssd_match = 1 and flag_prev_msed_match = 1 and flag_prev_bt_match = 1 and flag_prev_ss_fbcs_vista = 0 and flag_prev_ss_vista_vista = 0 and flag_prev_cta_match = 1))

;

--n=

 

--Ck

select top 100 * from #ipcost5i; 

 

/*Roll up*/

drop table if exists #ipcost6i;

select patient_icn, admitid, min_service_start_date, max_service_end_date, bill_type, source_system, sum(claim_total_amount) as claim_total_amount

into #ipcost6i

from #ipcost5i

group by patient_icn, admitid, min_service_start_date, max_service_end_date, source_system, bill_type;

--n=

 

--Ck

select top 100 * from #ipcost6i order by admitid;

 

/*Professional records*/

--Pull admitid, min_service_start_date, and max_service_end_date from institutional file

drop table if exists #ipcost7i;

select patient_icn, admitid, min_service_start_date, max_service_end_date into #ipcost7i from #ipcost6i;

--n=

 

--Merge in professional records

--Search for professional records within inpatient stay window w/ -3 day buffer

drop table if exists #ipcost2p;

select  dateadd(day, -3, l.min_service_start_date) as min_service_start_date_3

,l.min_service_start_date

,l.max_service_end_date

,r.*

into #ipcost2p

from #ipcost7i l 

left join #ipcost1p r 

on l.patient_icn=r.patient_icn and r.service_end_date>=dateadd(day, -3, l.min_service_start_date) and r.service_start_date<=l.max_service_end_date 

where r.patient_icn is not null;

--n=

 

--Ck

select top 100 * from #ipcost2p order by patient_icn, service_start_date, service_end_date; 

 

--Append professional records from patients that did not have an inpatient stay to merge with 

drop table if exists #ipcost2p2;

select *

into #ipcost2p2

from #ipcost1p

where patient_icn not in (select patient_icn from #ipcost2p);

--n=

 

--Ck

select top 100 * from #ipcost2p2 order by patient_icn; 

 

drop table if exists #ipcost2p3;

select patient_icn, admitid, original_claimid, min_service_start_date, max_service_end_date, place_of_service_id, source_system, claim_total_amount 

into #ipcost2p3

from #ipcost2p

union all

select patient_icn, admitid, original_claimid, service_start_date as min_service_start_date, service_end_date as max_service_end_date, place_of_service_id, source_system, claim_total_amount 

from #ipcost2p2;

--n=

 

--Ck

select top 100 * from #ipcost2p3 order by patient_icn, min_service_start_date, max_service_end_date, claim_total_amount, source_system; 

 

/*Identify and resolve possible duplicates within source system. If multiple rows have same

- original_claimid

then, calculate min, mean, max, and sum of claim_total_amount.

Researcher can decide which value to use moving forward. For now, we use max_cta.

*/

drop table if exists #ipcost2p3_;

select patient_icn, original_claimid, min(claim_total_amount) as min_cta, avg(claim_total_amount) as avg_cta, max(claim_total_amount) as max_cta, sum(claim_total_amount) as sum_cta

into #ipcost2p3_

from #ipcost2p3

group by patient_icn, original_claimid;

--n=

 

--Ck

select top 100 * from #ipcost2p3_ order by patient_icn;

 

--Merge back and collapse

drop table if exists #ipcost2p3__;

select distinct l.min_service_start_date, l.max_service_end_date, l.patient_icn, l.admitid,  l.original_claimid, l.source_system, l.place_of_service_id,  r.max_cta as claim_total_amount

into #ipcost2p3__

from #ipcost2p3 l 

left outer join #ipcost2p3_ r 

on l.patient_icn=r.patient_icn and l.original_claimid=r.original_claimid;

--n=

 

--Ck

select top 100 * from #ipcost2p3__ order by patient_icn;

 

 

/*De-duplicate across source system. Dedupe if rows have same 

- min_service_start_date

- max_service_end_date

- place_of_service_id 

- claim_total_amount

 

- Flag records that indicate FBCS followed by VISTA

- Flag records that indicate VISTA followed by VISTA

- Modify code below if you'd like to dedupe by other combinations of source systems, e.g. FBCS and eCAMS

*/

drop table if exists #ipcost3p;

select *

, case when lag(min_service_start_date) over (partition by admitid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = min_service_start_date then 1 else 0 end as flag_prev_mssd_match

, case when lag(max_service_end_date) over (partition by admitid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = max_service_end_date then 1 else 0 end as flag_prev_msed_match

, case when lag(place_of_service_id) over (partition by admitid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = place_of_service_id then 1 else 0 end as flag_prev_pos_match

, case when lag(source_system) over (partition by admitid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = 'FBCS' and source_system='VISTA' then 1 else 0 end as flag_prev_ss_fbcs_vista

, case when lag(source_system) over (partition by admitid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = 'VISTA' and source_system='VISTA' then 1 else 0 end as flag_prev_ss_vista_vista

, case when lag(claim_total_amount) over (partition by admitid, min_service_start_date, max_service_end_date, claim_total_amount order by admitid, min_service_start_date, max_service_end_date, claim_total_amount, source_system) = claim_total_amount then 1 else 0 end as flag_prev_cta_match

into #ipcost3p

from #ipcost2p3 

;

--n=

 

--Ck

select top 100 * from #ipcost3p; 

 

--De-duplicate by keeping only those records w/ flags (0,0,0,0,0,0) or (1,1,1,0,0,1) aka drop dupe VISTA records

drop table if exists #ipcost4p;

select *

into #ipcost4p 

from #ipcost3p

where ((flag_prev_mssd_match = 0 and flag_prev_msed_match = 0 and flag_prev_pos_match = 0 and flag_prev_ss_fbcs_vista = 0 and flag_prev_ss_vista_vista = 0 and flag_prev_cta_match = 0) or 

(flag_prev_mssd_match = 1 and flag_prev_msed_match = 1 and flag_prev_pos_match = 1 and flag_prev_ss_fbcs_vista = 0 and flag_prev_ss_vista_vista = 0 and flag_prev_cta_match = 1))

;

--n=

 

--Ck

select top 100 * from #ipcost4p; 

 

/*Roll up*/

drop table if exists #ipcost5p;

select patient_icn, admitid, min_service_start_date, max_service_end_date, place_of_service_id, source_system, sum(claim_total_amount) as claim_total_amount

into #ipcost5p

from #ipcost4p

group by patient_icn, admitid, min_service_start_date, max_service_end_date, source_system, place_of_service_id;

--n=

 

--Ck

select top 100 * from #ipcost5p;

 

/*Stack institutional and professional claims*/

drop table if exists #ipcost1ip;

select patient_icn, admitid, min_service_start_date, max_service_end_date, bill_type, '' as place_of_service_id, source_system, claim_total_amount

into #ipcost1ip

from #ipcost6i

union

select patient_icn, admitid, min_service_start_date, max_service_end_date, '' as bill_type, place_of_service_id, source_system, claim_total_amount

from #ipcost5p;

--n=

 

--Ck

select top 100 * from #ipcost1ip order by patient_icn, min_service_start_date, max_service_end_date, bill_type, place_of_service_id, source_system, claim_total_amount; 

 

/*Calculate inpatient total cost (sum institutional + professional fees per min_service_start_date and max_service_end_date)*/

drop table if exists #ipcost2ip;

select patient_icn, min_service_start_date, max_service_end_date, sum(claim_total_amount) as inpatient_claim_total_amount

into #ipcost2ip

from #ipcost1ip

group by patient_icn, min_service_start_date, max_service_end_date;

--n=

 

--Ck

select top 100 * from #ipcost2ip order by patient_icn, min_service_start_date, max_service_end_date; 

Last updated: July 18, 2024