Versions Compared

Key

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

...

...

...

...

...

...

...

...

Summary

The entity_ascs table contains information on the entities that are considered Ambulatory Surgical Centers. It contains details on the ASC and its operating specifications. The entity_id field links to the entities table, having the same id existing in both tables and acting as a key.

...

 

Table Creation

DROP TABLE IF EXISTS `entity_astcs`;

CREATE TABLE entity_astcs ( 
`entity_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`aha_id` varchar(7) unsigned DEFAULT NULL, 
`mpn_id` varchar(15) DEFAULT NULL, 
`npi_id` int(10) unsigned DEFAULT NULL, 
`local_id` varchar(15) DEFAULT NULL, 
`name` varchar(255) DEFAULT NULL, 
`aka` varchar(255) DEFAULT NULL, 
`address` varchar(60) DEFAULT NULL, 
`city` varchar(30) DEFAULT NULL, 
`state` enum ('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','UT','VA','VI','VT','WA','WI','WV','WY','US') DEFAULT NULL, 
`state_fips` varchar(10) DEFAULT NULL, 
`zipcode` varchar(5) DEFAULT NULL, 
`county_name` varchar(25) DEFAULT NULL, 
`county_fips` varchar(10) DEFAULT NULL, 
`phone_number` varchar(15) 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') NOT NULL DEFAULT 'entity', 
`geo_lat` double DEFAULT NULL, 
`geo_long` double DEFAULT NULL, 
`hsa_code` varchar(255) DEFAULT NULL, 
`hrr_code` varchar(255) DEFAULT NULL, 
`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`), 
CONSTRAINT `entity_astcs_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE -- Pellucid only 
constraint 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


 

Field Information

`entity_id` the identifying entity number. 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) 
`name` formal business name that is defined by CMS 
`aka` alias for the hospital, derived either from intel or AHA 
`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` provided by national AHA survey 
`zipcode` 
`county_name` 
`county_fips` provided by national AHA survey 
`phone_number` 
`ownership` Ownership defined by CMS 
`ipro_cms_ownership_code` Collapsed grouping performed by IPRO 'For Profit','Government Federal','Government Non Federal','Not For Profit' 
`aha_ownership` Ownership reported to AHA 
`aha_ownership_code` Collapsed grouping performed by IPRO 'For Profit','Government Federal','Government Non Federal','Not For Profit' 
`ipro_aha_ownership_group` 
`entity_type` determines the hospital's classification('comparator','hrr','system','entity') 
`hsa_code` HSA code derived from Dartmouth 
`hrr_code` HRR code derived from Dartmouth 
`geo_lat` latitude location 
`geo_long` longitude location 
`hsa_code` HSA code derived from Dartmouth 
`hrr_code` HSA code derived from Dartmouth 
`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
entity_idBIGINT(20)UNSIGNED NOT NULL AUTO_INCREMENTProvided by Pellucid, same as entities table1219198502
aha_idVARCHAR(7)DEFAULT NULLProvided by AHA National Survey161514131
mpn_idVARCHAR(15)DEFAULT NULLProvided by CMS Data301912
npi_idINT(10)DEFAULT NULLDerived from Government registry1918445361
local_idVARCHAR(15)DEFAULT NULLDerived from state information1234568A34D
nameVARCHAR(255)DEFAULT NULLDefined by CMSSaint Joseph's Rehabilitation
akaVARCHAR(255)DEFAULT NULLDerived from intel or AHASt. Joe's Rehab
addressVARCHAR(60)DEFAULT NULLPhysical Location123 Fake St
cityVARCHAR(30)DEFAULT NULLPhysical LocationLake Success
stateENUMDEFAULT NULLPhysical LocationNY
state_fipsVARCHAR(10)DEFAULT NULLProvided by AHA09
zipcodeVARCHAR(5)DEFAULT NULLPhysical Location11040
county_nameVARCHAR(25)DEFAULT NULLPhysical LocationNassau
county_fipsVARCHAR(10)DEFAULT NULLProvided by AHA036
phone_numberVARCHAR(15)DEFAULT NULLTelephone Number5165550123
ownershipVARCHAR(100)DEFAULT NULLDefined from CMSGovernment - Hospital District or Authority
ipro_cms_ownsership_groupVARCHAR(50)DEFAULT NULLPerformed by IPROFor Profit
aha_ownershipVARCHAR(50)DEFAULT NULLOwnership reported to AHAGovernment, Nonfederal-County
aha_ownership_codeVARCHAR(255)DEFAULT NULLProvided by AHA13
ipro_aha_ownership_groupVARCHAR(50)DEFAULT NULLPerformed by IPROGovernment Non Federal
entity_typeenum('comparator','hrr','system','entity')NOT NULL DEFAULT 'entity'hospital's classificationentity
geo_latdoubleDEFAULT NULLLatitude Location32.3272
geo_longdoubleDEFAULT NULLLongitude Location86.2712
hsa_codeVARCHAR(255)DEFAULT NULLDerived from Dartmouth12A34
hrr_codeVARCHAR(255)DEFAULT NULLDerived from Dartmouth56B78
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 (`entity_id`), 
CONSTRAINT `entity_astcs_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE -- Pellucid only constraint

 

Notes:

Currently, the only ASCs tracked in pellucid are from Illinois, as their hospital report card provides and reports on quality and performance data.  The Illinois Department of Public Health provides these metrics personally.  Generally ASC performance data is not readily available.