entity_ascs

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.

The entity_ascs table also links to the following tables:

• entities
• relationship_ascs_hospitals
• entity_asc_overrides
• entity_asc_attributes

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

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.