COTD File Format

Catch of the Day includes the following spreadsheets:

Where This Happened (Diagnosis or Procedure)

This spreadsheet gives you the complete demographics for every patient in our database that had this diagnosis or procedure. The demographics are broken down by region of the country, age range of patient, gender, provider type, year, and quarter

Columns include:

• Key (explained below)
• Age Range of patients
• Record Count - Number of medical records in our database with this procedure or diagnosis
• Patient Count - Number of distinct patients in our database with this procedure or diagnosis

Associated Diagnoses (Diagnosis or Procedure)

This spreadsheet gives you all the diagnoses that appeared in the same patient records that included diagnosis or procedure that you requested.

Columns Include:

• Diagnosis code
• Provider Type (inpatient, outpatient, physician, or all providers)
• Record Count - Number of medical records that had both the catch of the day procedure/diagnosis code AND the diagnosis code listed in the Diagnosis column.

Associated Procedures (Diagnosis or Procedure)

This spreadsheet gives all the procedures that appeared in the same patient records that included the diagnosis or procedure that you requested. You should note that if you are looking up a CPT procedure code, there will be no associated procedures generated since our patient records only contains a single CPT procedure code per record.

Columns Include:

• Diagnosis code
• Provider Type (inpatient, outpatient, physician, or all providers)
• Record Count - Number of medical records that had both the catch of the day procedure/diagnosis code AND the procedure code listed in the Procedure column

Patient Tracking Before (Diagnosis or Procedure)

This spreadsheet gives you all of the procedures that happened to all patients BEFORE they had the procedure of interest. It also gives you the charging information for these procedures. Also included is an additional record for each time period called "Total" which gives you the total charges and average charges for patients in that time period.

Time periods shown include:

• 14 days before
• 30 days before
• 90 days before
• 6 months before
• 1 year before
• 2 years before

Columns Include:

• Days - number of days before the procedure
• Procedure - Procedure that occurred within this time period
• Patient Count - How many distinct patients had the procedure within the time period
• Record Count - How many times the procedure occurred within this time period
• Total Charge - Total dollar amount charged for the procedure
• Average Patient Charge - Average per patient charge

Patient Tracking After (Diagnosis or Procedure)

To provide you with information throughout the continuum of patient care, this spreadsheet gives you all of the procedures and diagnoses that happened to all patients either the same day of the procedure, or any time AFTER they had the procedure you requested. It also gives you the charging information for these procedures. Also included is are additional records for each time period called "Total Charges" and "Average Charges," which give you the total and average charges for patients in that time period.

Time periods shown include:

• Same Day
• 14 days before
• 30 days before
• 90 days before
• 6 months before
• 1 year before
• 2 years before

Columns Include:

• Days - number of days after the procedure
• Procedure - Procedure that occurred within this time period
• Patient Count - How many distinct patients had the procedure within the time period
• Record Count - How many times the procedure occurred within this time period
• Total Charge - Total dollar amount charged for the procedure
• Average Patient Charge - Average per patient charge

What this Cost (Diagnosis)

This spreadsheet gives you the charges resulting from each diagnosis broken down by region, age of the patient, gender, provider type, year, and quarter. This COTD also file will offer you a unique window into the continuum of care for your searched diagnoses. You will see the number of patients and procedures as well as the total and average charges over a two year time frame, all resulting from one diagnosis.

Diagnostic Time Periods Include:

• 90 days
• 180 days
• 1 year
• 2 years
• > 2 years

Columns Include:

• Key (explained below)
• Age Range of patients
• Patient Count How many distinct patients had the procedure within the time period
• Procedure Count How many procedures are on record for the number of patients with the searched diagnosis
• Total Charge Total dollar amount charged
• Average Per Patient Charge Total dollar amount / Record Count

What This Cost (Procedure)

This spreadsheet gives you the charges resulting from your searched procedure broken down by region, age of the patient, gender, provider type, year, and quarter. We also give you the average and total length of stay (this will be zero unless the provider type is inpatient) along with the average and daily charges associated with the diagnosis or procedure.

Columns Include:

• Key (explained below)
• Age Range of patients
• Record Count Number of medical records in our database with this procedure or diagnosis
• Total Charge Total dollar amount charged
• Average Charge Total dollar amount / Record Count
• Total Length of Stay (LOS) Number of days spent in a hospital for this procedure
• Average LOS Total Length of Stay / Record Count
• Daily Charge Charges incurred for all procedures the same day as this procedure

Key Format

Two spreadsheets contain demographic information:

• [code]_whereThisHappened
• [code]_whatThisCost)

Within each spreadsheet is a "Key" column that embeds all the demographic information you need to know. The key looks like the following:

rr_ttt_g_a_yyyy_qq

• rr = Region (either MW, SO, NE, or WE)
• ttt = Provider type (either outpatient (out), inpatient(in), or physician (phy))
• g = Gender (M or F)
• a = age code (A – Z). Age codes are defined within the Age column of the spreadsheet
• yyyy = year (2004-2007)
• qq = quarter of the year (Q1 - Q4)

Key Example

So, if I wanted to find demographics for male inpatients in the south with age code N (which stands for ages 55-59) in the second quarter of 2004, I would find the key SO_in_M_N_2004_Q2.

But let's say that you really need to find the same demographics, but you wanted to see all age codes and not just the N age code. We have a special character * that can be used in any position of the Key that signifies all values have been rolled up into one.

So in this example, to see all age codes rolled up you would find the key SO_in_M_*_2004_Q2. You can use the * in multiple positions in the Key.

For example, the Key SO_*_M_*_2004_Q2 would give you all age ranges and all provider types for males in the south for the second quarter of 2004.