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 CreationDROP 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
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;
`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
Fieldsfield type attributes notes example entity_ascs.entity_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT Provided by Pellucid, same as FOREIGN KEY: links to entities table 1219198502 entity_ascs.aha_id VARCHAR(7) DEFAULT NULL Provided by AHA National Survey 161514131 entity_ascs.mpn_id VARCHAR(15) DEFAULT NULL Provided by CMS Data 301912 entity_ascs.npi_id INT(10) DEFAULT NULL Derived from Government registry 1918445361 entity_ascs.local_id VARCHAR(15) DEFAULT NULL Derived from state information 1234568A34D entity_ascs.name VARCHAR(255) DEFAULT NULL Defined by CMS Saint Joseph's Rehabilitation entity_ascs.aka VARCHAR(255) DEFAULT NULL Derived from intel or AHA St. Joe's Rehab entity_ascs.address VARCHAR(60) DEFAULT NULL Physical Location 123 Fake St entity_ascs.city VARCHAR(30) DEFAULT NULL Physical Location Lake Success entity_ascs.state ENUM DEFAULT NULL Physical Location NY entity_ascs.state_fips VARCHAR(10) DEFAULT NULL Provided by AHA 09 entity_ascs.zipcode VARCHAR(5) DEFAULT NULL Physical Location 11040 entity_ascs.county_name VARCHAR(25) DEFAULT NULL Physical Location Nassau entity_ascs.county_fips VARCHAR(10) DEFAULT NULL Provided by AHA 036 entity_ascs.phone_number VARCHAR(15) DEFAULT NULL Telephone Number 5165550123 entity_ascs.ownership VARCHAR(100) DEFAULT NULL Defined from CMS Government - Hospital District or Authority entity_ascs.ipro_cms_ownsership_group VARCHAR(50) DEFAULT NULL Performed by IPRO For Profit entity_ascs.aha_ownership VARCHAR(50) DEFAULT NULL Ownership reported to AHA Government, Nonfederal-County entity_ascs.aha_ownership_code VARCHAR(255) DEFAULT NULL Provided by AHA 13 entity_ascs.ipro_aha_ownership_group VARCHAR(50) DEFAULT NULL Performed by IPRO Government Non Federal entity_ascs.entity_type enum('comparator','hrr','system','entity') NOT NULL DEFAULT 'entity' hospital's classification entity entity_ascs.geo_lat double DEFAULT NULL Latitude Location 32.3272 entity_ascs.geo_long double DEFAULT NULL Longitude Location 86.2712 entity_ascs.hsa_code VARCHAR(255) DEFAULT NULL Derived from Dartmouth 12A34 entity_ascs.hrr_code VARCHAR(255) DEFAULT NULL Derived from Dartmouth 56B78 entity_ascs.closed_date date DEFAULT NULL Date of closure YYYY-MM-DD 2001-01-24 entity_ascs.created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' The date and time the row was created 2010-05-18 09:15:36 entity_ascs.modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP The date and time the row was last modified 2010-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.
| field | type | attributes | notes | example |
|---|---|---|---|---|
| entity_ascs.entity_id | BIGINT(20) | UNSIGNED NOT NULL AUTO_INCREMENT | Provided by Pellucid, same as FOREIGN KEY: links to entities table | 1219198502 |
| entity_ascs.aha_id | VARCHAR(7) | DEFAULT NULL | Provided by AHA National Survey | 161514131 |
| entity_ascs.mpn_id | VARCHAR(15) | DEFAULT NULL | Provided by CMS Data | 301912 |
| entity_ascs.npi_id | INT(10) | DEFAULT NULL | Derived from Government registry | 1918445361 |
| entity_ascs.local_id | VARCHAR(15) | DEFAULT NULL | Derived from state information | 1234568A34D |
| entity_ascs.name | VARCHAR(255) | DEFAULT NULL | Defined by CMS | Saint Joseph's Rehabilitation |
| entity_ascs.aka | VARCHAR(255) | DEFAULT NULL | Derived from intel or AHA | St. Joe's Rehab |
| entity_ascs.address | VARCHAR(60) | DEFAULT NULL | Physical Location | 123 Fake St |
| entity_ascs.city | VARCHAR(30) | DEFAULT NULL | Physical Location | Lake Success |
| entity_ascs.state | ENUM | DEFAULT NULL | Physical Location | NY |
| entity_ascs.state_fips | VARCHAR(10) | DEFAULT NULL | Provided by AHA | 09 |
| entity_ascs.zipcode | VARCHAR(5) | DEFAULT NULL | Physical Location | 11040 |
| entity_ascs.county_name | VARCHAR(25) | DEFAULT NULL | Physical Location | Nassau |
| entity_ascs.county_fips | VARCHAR(10) | DEFAULT NULL | Provided by AHA | 036 |
| entity_ascs.phone_number | VARCHAR(15) | DEFAULT NULL | Telephone Number | 5165550123 |
| entity_ascs.ownership | VARCHAR(100) | DEFAULT NULL | Defined from CMS | Government - Hospital District or Authority |
| entity_ascs.ipro_cms_ownsership_group | VARCHAR(50) | DEFAULT NULL | Performed by IPRO | For Profit |
| entity_ascs.aha_ownership | VARCHAR(50) | DEFAULT NULL | Ownership reported to AHA | Government, Nonfederal-County |
| entity_ascs.aha_ownership_code | VARCHAR(255) | DEFAULT NULL | Provided by AHA | 13 |
| entity_ascs.ipro_aha_ownership_group | VARCHAR(50) | DEFAULT NULL | Performed by IPRO | Government Non Federal |
| entity_ascs.entity_type | enum('comparator','hrr','system','entity') | NOT NULL DEFAULT 'entity' | hospital's classification | entity |
| entity_ascs.geo_lat | double | DEFAULT NULL | Latitude Location | 32.3272 |
| entity_ascs.geo_long | double | DEFAULT NULL | Longitude Location | 86.2712 |
| entity_ascs.hsa_code | VARCHAR(255) | DEFAULT NULL | Derived from Dartmouth | 12A34 |
| entity_ascs.hrr_code | VARCHAR(255) | DEFAULT NULL | Derived from Dartmouth | 56B78 |
| entity_ascs.closed_date | date | DEFAULT NULL | Date of closure YYYY-MM-DD | 2001-01-24 |
| entity_ascs.created | timestamp | NOT NULL DEFAULT '0000-00-00 00:00:00' | The date and time the row was created | 2010-05-18 09:15:36 |
| entity_ascs.modified | timestamp | NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP | The date and time the row was last modified | 2010-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
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.