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

How full are hospital beds by metropolitan area, in a given state? Includes only metropolitan areas reporting more than 50 staffed beds. Enter the state's two digit postal code below and hit 'Run SQL'.

Note that this query leaves out hospitals averaging less than 4 COVID patients 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.

Overall room rate is calculated as
room_fate = all_adult_hospital_inpatient_bed_occupied_7_day_avg /all_adult_hospital_inpatient_beds_7_day_avg
covid_room_rate = total_adult_patients_hospitalized_confirmed_and_suspected_covid_7_day_avg /all_adult_hospital_inpatient_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 (hide)

select  cbsatitle as metro_area, cbsacode, count(*) as num_hospitals, (0.0 + sum(total_adult_patients_hospitalized_confirmed_and_suspected_covid_7_day_avg))/sum(all_adult_hospital_inpatient_beds_7_day_avg) as covid_room_fraction, (0.0 + sum(all_adult_hospital_inpatient_bed_occupied_7_day_avg))/sum(all_adult_hospital_inpatient_beds_7_day_avg) as room_rate, sum(all_adult_hospital_inpatient_beds_7_day_avg) as total_staffed_beds, sum(total_adult_patients_hospitalized_confirmed_and_suspected_covid_7_day_avg) as total_covid_patients from   hosp_capacity left join cbsaxfips on hosp_capacity.fips_code = cbsaxfips.fips_code where  not all_adult_hospital_inpatient_beds_7_day_avg = '' and total_adult_patients_hospitalized_confirmed_and_suspected_covid_7_day_avg > 0 and all_adult_hospital_inpatient_beds_7_day_avg > 0  and all_adult_hospital_inpatient_bed_occupied_7_day_avg > 0 and total_adult_patients_hospitalized_confirmed_and_suspected_covid_7_day_avg > 0 and state = :state and collection_week = '2021/09/17' group by cbsacode, cbsatitle having sum(all_adult_hospital_inpatient_beds_7_day_avg) > 50 order by covid_room_fraction desc;

Query parameters

Edit SQL

0 results

Powered by Datasette · Query took 1.016ms