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 16 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/04/09' 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
College Station-Bryan, TX 17780 1 0.37106918238993714 0.8805031446540881 15.9 5.9
Midland, TX 33260 1 0.33098591549295775 0.823943661971831 28.4 9.4
Brownsville-Harlingen, TX 15180 2 0.22651006711409397 0.7768456375838927 59.6 13.5
Houston-The Woodlands-Sugar Land, TX 26420 22 0.2085740346609912 0.8714654910307087 1315.6000000000001 274.40000000000003
San Antonio-New Braunfels, TX 41700 4 0.19736273238218763 0.9351491569390402 462.6 91.3
El Paso, TX 21340 4 0.18969346956908043 0.8480675255442026 225.1 42.7
Odessa, TX 36220 1 0.1891891891891892 0.9039039039039041 33.3 6.3
McAllen-Edinburg-Mission, TX 32580 5 0.18773157678056815 0.654178674351585 242.9 45.6
Dallas-Fort Worth-Arlington, TX 19100 19 0.16963573517681463 0.9206328104227601 752.2 127.59999999999998
Amarillo, TX 11100 2 0.16109785202863963 0.8377088305489259 83.8 13.5
Beaumont-Port Arthur, TX 13140 2 0.1537242472266244 0.9001584786053882 63.1 9.7
Killeen-Temple, TX 28660 1 0.1295180722891566 1.0 66.4 8.6
Corpus Christi, TX 18580 2 0.12307692307692308 0.8272727272727273 143.0 17.6
Austin-Round Rock, TX 12420 4 0.12118607649333905 0.8689299527288354 232.7 28.199999999999996
Tyler, TX 46340 2 0.08864602307225258 0.9295689131754706 164.7 14.6
Lubbock, TX 31180 1 0.08040201005025127 0.8655778894472363 79.6 6.4
Powered by Datasette · Query took 77.756ms