Hospitals Table Changelog

Summary

The tables 'entity_hospitals_update_hist' and  'entity_hospitals_update_hist_detail' contain information on changes made to the 'entity_hospitals' table. 'entity_hospitals_update_hist_detail' contains details on each specific change to the data while 'entity_hospitals_update_hist' keeps a documents the hospital records before each change.

Entity hospitals update hist detail

The latest version of this table is entity_hospitals_update_hist_detail - May 4th 2015

Table Creation

DROP TABLE IF EXISTS `entity_hospitals_update_hist_detail`; 

CREATE TABLE IF NOT EXISTS `entity_hospitals_update_hist_detail` (
  `update_hist_detail_id` int(10) NOT NULL AUTO_INCREMENT,
  `entity_id` bigint(20) unsigned DEFAULT NULL,
  `col_name` varchar(150) DEFAULT NULL,
  `old_value` varchar(500) DEFAULT NULL,
  `new_value` varchar(500) DEFAULT NULL,
  `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`update_hist_detail_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=50590 ;

Field Information

`update_hist_detail_id` the unique identifying number.
`entity_id` Same as entity_id in the entities table, and serves as a link 
`col_name` name of the column where the data was changed 
`old_value` prior value in the column 
`new_value` new value in the column 
`updated` date and time the row was modified in YYYY-MM-DD HH:MM:SS format. Can be set to current ON UPDATE CURRENT_TIMESTAMP
`updated_by` user that performed the update 

Fields

PRIMARY KEY (`update_hist_detail_id`)

Entity hospitals update hist

The latest version of this table is entity_hospitals_update_hist - May 4th 2015

Table Creation

DROP TABLE IF EXISTS `entity_hospitals_update_hist`; 

CREATE TABLE IF NOT EXISTS `entity_hospitals_update_hist` (
  `hist_id` int(10) NOT NULL AUTO_INCREMENT,
  `entity_id` bigint(20) unsigned DEFAULT NULL,
  `aha_id` varchar(7) DEFAULT NULL,
  `mpn_id` varchar(15) DEFAULT NULL,
  `npi_id` varchar(10) DEFAULT NULL,
  `local_id` varchar(15) DEFAULT NULL,
  `aha_sys_id` varchar(4) DEFAULT NULL,
  `aha_service_id` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `aka` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` enum('AK','AL','AS','AR','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY',
  'LA','MA','MD','ME','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR',
  'PA','PR','RI','SC','SD','TN','TX','US','UT','VA','VI','VT','WA','WI','WV','WY') DEFAULT NULL,
  `state_fips` varchar(10) DEFAULT NULL,
  `zipcode` varchar(5) DEFAULT NULL,
  `county_name` varchar(30) DEFAULT NULL,
  `county_fips` varchar(10) DEFAULT NULL,
  `phone_number` varchar(46) DEFAULT NULL,
  `ownership` varchar(100) DEFAULT NULL,
  `ipro_cms_ownership_group` varchar(50) DEFAULT NULL,
  `aha_ownership` varchar(50) DEFAULT NULL,
  `aha_ownership_code` varchar(255) DEFAULT NULL,
  `ipro_aha_ownership_group` varchar(50) DEFAULT NULL,
  `entity_type` enum('comparator','hrr','system','entity','Critical Access Hospitals') NOT NULL DEFAULT 'entity',
  `hsa_code` varchar(5) DEFAULT NULL,
  `hrr_code` varchar(3) DEFAULT NULL,
  `geo_lat` float DEFAULT NULL,
  `geo_long` float DEFAULT NULL,
  `aa_jc` tinyint(1) DEFAULT '0',
  `aa_cp` tinyint(1) DEFAULT '0',
  `aa_rt` tinyint(1) DEFAULT '0',
  `aa_ms` tinyint(1) DEFAULT '0',
  `aa_ns` tinyint(1) DEFAULT '0',
  `aa_ca` tinyint(1) DEFAULT '0',
  `aa_co` tinyint(1) DEFAULT '0',
  `aa_bc` tinyint(1) DEFAULT '0',
  `aa_md` tinyint(1) DEFAULT '0',
  `aa_ao` tinyint(1) DEFAULT '0',
  `aa_cc` tinyint(1) DEFAULT '0',
  `aa_hs` tinyint(1) DEFAULT '0',
  `aa_mc` tinyint(1) DEFAULT '0',
  `aa_sn` tinyint(1) DEFAULT '0',
  `aa_ph` tinyint(1) DEFAULT '0',
  `closed_date` date DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=45940 ;

Field Information

`hist_id` the unique identifying number.
`entity_id` Same as entity_id in the entities table, and serves as a link 
`aha_id` number assigned to the hospital in the AHA national survey 
`mpn_id` derived from CMS data 
`npi_id` national provider number derived from https://nppes.cms.hhs.gov/NPPES/NPIRegistryHome.do 
`local_id` provider ID assigned by source (i.e. state) 
`aha_sys_id` if entity is a member of a multi-entity system the system can be stored here and looked up in ref_systems. Primarily obtained from AHA Survey database. 
`aha_service_id` AHA assigns various service codes to denote the type of hospital 
`name` formal business name that is defined by CMS 
`aka` alias for the hospital 
`address` physical location of the establishment 
`city` 
`state` the two-letter state abbreviation ('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL', 
'IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR', 
'PA','PR','RI','SC','SD','TN','TX','US','UT','VA','VI','VT','WA','WI','WV','WY'). 
Default is null and national entities have a state code of 'US' 
`state_fips` FIPS state code 
`zipcode` 
`county_name` 
`county_fips` FIPS county code 
`phone_number` 
`entity_type` determines the hospital's classification('comparator','hrr','system','entity') 
`hsa_code` HSA  identifier (Dartmouth)  
`hrr_code` HRR identifier (Dartmouth) 
`geo_lat` latitude location 
`geo_long` longitude location 
`aa_jc` is Joint Commission accredited? (1=Y 0=N) 
`aa_cp` has American College of Surgeons approved Cancer Program? (1=Y 0=N) 
`aa_rt` has Residency Training approved by ACGME? (1=Y 0=N) 
`aa_ms` has a medical school affiliation? (1=Y 0=N) 
`aa_ns` is a National League of Nursing nursing school? (1=Y 0=N) 
`aa_ca` is CARF accredited? (1=Y 0=N) 
`aa_co` is a member of COTH? (1=Y 0=N) 
`aa_bc` is Blue Cross contracting or participating? (1=Y 0=N) 
`aa_md` is Medicare certified? (1=Y 0=N) 
`aa_ao` is American Osteopathic Association accredited? (1=Y 0=N) 
`aa_cc` is Catholic Church operated? (1=Y 0=N) 
`aa_hs` is member Federation of American Health Care Systems? (1=Y 0=N) 
`aa_mc` is an academic medical center? (1=Y 0=N).  Found at http://services.aamc.org/memberlistings/index.cfm?fuseaction=home.search&search_type=TH&state_criteria=ALL
`aa_sn` is a CMWF Safety Net Hospital? (1=Y 0=N) 
`aa_ph` is a member National Association Public Hospitals (NAPH) (1=Y 0=N) 
`closed_date` date the entity was closed in YYYY-MM-DD format 
`created` date and time the row was created in YYYY-MM-DD HH:MM:SS format 
`modified` date and time the row was modified in YYYY-MM-DD HH:MM:SS format. Can be set to current ON UPDATE CURRENT_TIMESTAMP

Fields

PRIMARY KEY (`hist_id`)