Versions Compared

Version Old Version 1 New Version 2
Changes made by

Alex Laird (Unlicensed)

Alex Laird (Unlicensed)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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.

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
fieldtypeattributesnotesexample
hist_idINT(10)NOT NULL AUTO_INCREMENTUnique ID12345
entity_idBIGINT(20)UNSIGNED DEFAULT NULLProvided by Pellucid, same as entities table1219198502
aha_idVARCHAR(7)DEFAULT NULLProvided by AHA National Survey161514131
mpn_idVARCHAR(15)DEFAULT NULLProvided by CMS Data301912
npi_idVARCHAR(10)DEFAULT NULLDerived from Government registry1918445361
local_idVARCHAR(15)DEFAULT NULLDerived from state information1234568A34D
aha_sys_idVARCHAR(4)DEFAULT NULLPellucid ID of the System it belongs to7890
aha_service_idVARCHAR(255)DEFAULT NULLAHA Denotation of hospital type007
nameVARCHAR(255)DEFAULT NULLDefined by CMSSaint Joseph's Rehabilitation
akaVARCHAR(255)DEFAULT NULL St. Joe's Rehab
addressVARCHAR(255)DEFAULT NULLPhysical Location123 Fake St
cityVARCHAR(255)DEFAULT NULL Lake Success
stateENUMDEFAULT NULL2 character abbreviation for the stateNY
state_fipsVARCHAR(10)DEFAULT NULLNationally defined09
zipcodeVARCHAR(5)DEFAULT NULL 11042
county_nameVARCHAR(30)DEFAULT NULL Nassau
county_fipsVARCHAR(10)DEFAULT NULL

Determined by state

056
phone_numberVARCHAR(46)DEFAULT NULL 5165550123
ownershipVARCHAR(100)DEFAULT NULLowner/operator type of the entity

Voluntary - Not For Profit

ipro_cms_ownership_groupVARCHAR(50)DEFAULT NULLPerformed by IPRONot For Profit
aha_ownershipVARCHAR(50)DEFAULT NULLOwnership reported to AHAFor Profit
aha_ownership_codeVARCHAR(255)DEFAULT NULLProvided by AHA23
ipro_aha_ownership_groupVARCHAR(50)DEFAULT NULLPerformed by IPRONot For Profit
entity_typeENUM('comparator','hrr','system','entity','Critical Access Hospitals')NOT NULL DEFAULT 'entity'hospital's classificationentity
hsa_codeVARCHAR(5)DEFAULT NULLDerived from Dartmouth12A34
hrr_codeVARCHAR(3)DEFAULT NULLDerived from Dartmouth56B78
geo_latFLOATDEFAULT NULLlatitude location32.3272
geo_longFLOATDEFAULT NULLlongitude location86.2712
aa_jcTINYINT(1) 1=Y 0=N1
aa_cpTINYINT(1) 1=Y 0=N1
aa_rtTINYINT(1) 1=Y 0=N1
aa_msTINYINT(1) 1=Y 0=N1
aa_nsTINYINT(1) 1=Y 0=N1
aa_caTINYINT(1) 1=Y 0=N1
aa_coTINYINT(1) 1=Y 0=N1
aa_bcTINYINT(1) 1=Y 0=N1
aa_mdTINYINT(1) 1=Y 0=N1
 aa_aoTINYINT(1) 1=Y 0=N1
 aa_ccTINYINT(1) 1=Y 0=N1
 aa_hsTINYINT(1) 1=Y 0=N1
 aa_mcTINYINT(1) 1=Y 0=N  1
 aa_snTINYINT(1) 1=Y 0=N1
aa_ph TINYINT(1) 1=Y 0=N1
closed_dateDATEDEFAULT NULLDate of closure YYYY-MM-DD2001-01-24
createdTIMESTAMPNOT NULL DEFAULT '0000-00-00 00:00:00'The date and time the row was created2010-05-18 09:15:36
modifiedTIMESTAMPNOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMPThe date and time the row was last modified2010-05-18 09:15:36


PRIMARY KEY (`hist_id`)