How full are ICUs summed by metropolitan area in a given state?

How full is the ICU at all reporting hospitals overall in metropolitan areas with more than 10 ICU beds in a given state? Enter the metropolitan area's cbsacode below and hit 'Run SQL'. To see hospital level detail for this metro area, copy the cbsacode and paste it into this query

Note that this query leaves out hospitals averaging less than 4 ICU COVID beds weekly, or that don't report their weekly staffed bed count, so consider the results an estimate only. This query also ignores lines where this data is blank.

The fractions are calculated as:
icu_occupancy_fraction = staffed_adult_icu_bed_occupancy_7_day_avg /total_staffed_adult_icu_beds_7_day_avg
How full is the ICU in terms of confirmed or suspected COVID patients? This is calculated as:
covid_icu_fraction = staffed_icu_adult_patients_confirmed_and_suspected_covid_7_day_avg /total_staffed_adult_icu_beds_7_day_avg

This database can be queried directly using Structured Query Language (SQL) supported by SQLite. For more on that syntax, see here. Read more about datasette, the technology powering these interactive queries.

Custom SQL query returning 38 rows (hide)

select  cbsatitle as metro_area, cbsacode, count(*) as num_hospitals, (0.0 + sum(staffed_icu_adult_patients_confirmed_and_suspected_covid_7_day_avg))/sum(total_staffed_adult_icu_beds_7_day_avg) as covid_icu_fraction, (0.0 + sum(staffed_adult_icu_bed_occupancy_7_day_avg))/sum(total_staffed_adult_icu_beds_7_day_avg) as icu_occupancy_fraction, sum(total_staffed_adult_icu_beds_7_day_avg) as total_staffed_icu_beds, sum(staffed_icu_adult_patients_confirmed_and_suspected_covid_7_day_avg) as total_covid_icu_beds from   hosp_capacity left join cbsaxfips on hosp_capacity.fips_code = cbsaxfips.fips_code where  not total_staffed_adult_icu_beds_7_day_avg = '' and staffed_icu_adult_patients_confirmed_and_suspected_covid_7_day_avg > 0 and staffed_adult_icu_bed_occupancy_7_day_avg > 0 and staffed_adult_icu_bed_occupancy_7_day_avg > 0 and state = :state and collection_week = '2021-01-15' group by cbsacode, cbsatitle having sum(total_staffed_adult_icu_beds_7_day_avg) > 10 order by covid_icu_fraction desc;

Query parameters

Edit SQL

This data as json, CSV

metro_areacbsacodenum_hospitalscovid_icu_fractionicu_occupancy_fractiontotal_staffed_icu_bedstotal_covid_icu_beds
Rio Grande City, TX 40100 1 0.9865771812080536 1.0 14.9 14.7
Brownwood, TX 15220 1 0.9490445859872612 1.0 15.7 14.9
Corsicana, TX 18620 1 0.7886178861788616 0.8373983739837398 12.3 9.7
Eagle Pass, TX 20580 1 0.7518248175182483 1.0 13.7 10.3
Paris, TX 37580 1 0.7486033519553074 0.9888268156424581 17.9 13.4
Laredo, TX 29700 3 0.7193923145665773 0.9910634495084896 111.9 80.5
Mount Pleasant, TX 34420 1 0.6810344827586208 0.9137931034482759 11.6 7.9
Lufkin, TX 31260 2 0.6651376146788992 0.8394495412844039 21.799999999999997 14.5
Huntsville, TX 26660 1 0.6635514018691588 1.0 10.7 7.1
Wichita Falls, TX 48660 1 0.6365422396856582 0.8978388998035364 50.9 32.4
Texarkana, TX-AR 45500 2 0.6271428571428571 0.8799999999999999 70.0 43.9
Longview, TX 30980 1 0.6159420289855072 0.927536231884058 27.6 17.0
San Angelo, TX 41660 1 0.606896551724138 0.7793103448275862 29.0 17.6
Abilene, TX 10180 2 0.6061588330632092 1.0 61.7 37.400000000000006
Victoria, TX 47020 2 0.5695142378559463 0.896147403685092 59.7 34.0
San Antonio-New Braunfels, TX 41700 11 0.5550607287449392 0.9448043184885287 741.0000000000001 411.3
Nacogdoches, TX 34860 2 0.5399239543726236 0.9125475285171104 26.299999999999997 14.200000000000001
Sulphur Springs, TX 44860 1 0.5307692307692308 0.9692307692307692 13.0 6.9
Palestine, TX 37300 1 0.5221238938053098 0.9646017699115044 11.3 5.9
Dallas-Fort Worth-Arlington, TX 19100 49 0.5190642290113064 0.9732980514794323 1662.7999999999997 863.1000000000003
Midland, TX 33260 1 0.516629711751663 0.844789356984479 45.1 23.3
College Station-Bryan, TX 17780 3 0.5140324963072378 1.0 67.7 34.8
El Paso, TX 21340 7 0.5129282482223658 0.9172592113768585 309.4 158.7
McAllen-Edinburg-Mission, TX 32580 6 0.5057265569076592 0.8897637795275589 279.40000000000003 141.3
Waco, TX 47380 2 0.5 0.9341085271317828 77.4 38.7
Amarillo, TX 11100 3 0.4969512195121951 0.8896341463414634 164.0 81.5
Austin-Round Rock, TX 12420 11 0.4787839020122484 0.8943569553805774 457.2 218.89999999999998
Tyler, TX 46340 4 0.47480988593155893 0.9500950570342205 210.4 99.9
Sherman-Denison, TX 43300 2 0.45820895522388067 0.9970149253731343 67.0 30.700000000000003
Kerrville, TX 28500 1 0.45 0.9714285714285714 14.0 6.3
Brownsville-Harlingen, TX 15180 5 0.445687952600395 0.8894009216589861 151.9 67.7
Athens, TX 11980 1 0.4315068493150685 0.8698630136986301 14.6 6.3
Houston-The Woodlands-Sugar Land, TX 26420 33 0.43057468386127457 0.9064730186553152 1597.3999999999999 687.8
Odessa, TX 36220 2 0.4058272632674298 0.7013527575442249 96.1 39.0
Beaumont-Port Arthur, TX 13140 3 0.4019501625135428 0.9425785482123511 92.3 37.1
Killeen-Temple, TX 28660 3 0.3768888888888889 0.9759999999999999 112.5 42.4
Lubbock, TX 31180 2 0.32686822589845976 0.8887621220764403 175.3 57.3
Corpus Christi, TX 18580 2 0.32558139534883723 0.9117647058823531 146.2 47.6
Powered by Datasette · Query took 48.377ms