HealthAnalyticsToolkit

Example with COVID Data

For this example I will be utilizing COVID-19 Reported Patient Impact and Hospital Capacity by State Timeseries.

Navigating to the link above can provide you with a full description of the dataset. In this tutorial, we are going to look at which states have the highest percentage of critical staffing shorages on a given date. These would be the states in which one would be most concerned about the staffing and may want to send aid.

The first thing we want to do is import the data into a table so that we can work with it.

IMPORT CSV "C:\Users\COVID-19_Reported_Patient_Impact_and_Hospital_Capacity_by_State_Timeseries.CSV" INTO covid_table;

To look at the table, we can use:

SELECT * FROM covid_table;

The table below displays what you should see (note only the first 10 columns of data have been included in this view to conserve space).

state	date	critical_staffing_shortage_today_yes	critical_staffing_shortage_today_no	critical_staffing_shortage_today_not_reported	critical_staffing_shortage_anticipated_within_week_yes	critical_staffing_shortage_anticipated_within_week_no	critical_staffing_shortage_anticipated_within_week_not_reported	hospital_onset_covid	hospital_onset_covid_coverage	inpatient_beds	inpatient_beds_coverage	inpatient_beds_used	inpatient_beds_used_coverage	inpatient_beds_used_covid	inpatient_beds_used_covid_coverage	previous_day_admission_adult_covid_confirmed	previous_day_admission_adult_covid_confirmed_coverage	previous_day_admission_adult_covid_suspected	previous_day_admission_adult_covid_suspected_coverage	previous_day_admission_pediatric_covid_confirmed	previous_day_admission_pediatric_covid_confirmed_coverage	previous_day_admission_pediatric_covid_suspected	previous_day_admission_pediatric_covid_suspected_coverage	staffed_adult_icu_bed_occupancy	staffed_adult_icu_bed_occupancy_coverage	staffed_icu_adult_patients_confirmed_and_suspected_covid	staffed_icu_adult_patients_confirmed_and_suspected_covid_coverage	staffed_icu_adult_patients_confirmed_covid	staffed_icu_adult_patients_confirmed_covid_coverage	total_adult_patients_hospitalized_confirmed_and_suspected_covid	total_adult_patients_hospitalized_confirmed_and_suspected_covid_coverage	total_adult_patients_hospitalized_confirmed_covid	total_adult_patients_hospitalized_confirmed_covid_coverage	total_pediatric_patients_hospitalized_confirmed_and_suspected_covid	total_pediatric_patients_hospitalized_confirmed_and_suspected_covid_coverage	total_pediatric_patients_hospitalized_confirmed_covid	total_pediatric_patients_hospitalized_confirmed_covid_coverage	total_staffed_adult_icu_beds	total_staffed_adult_icu_beds_coverage	inpatient_beds_utilization	inpatient_beds_utilization_coverage	inpatient_beds_utilization_numerator	inpatient_beds_utilization_denominator	percent_of_inpatients_with_covid	percent_of_inpatients_with_covid_coverage	percent_of_inpatients_with_covid_numerator	percent_of_inpatients_with_covid_denominator	inpatient_bed_covid_utilization	inpatient_bed_covid_utilization_coverage	inpatient_bed_covid_utilization_numerator	inpatient_bed_covid_utilization_denominator	adult_icu_bed_covid_utilization	adult_icu_bed_covid_utilization_coverage	adult_icu_bed_covid_utilization_numerator	adult_icu_bed_covid_utilization_denominator	adult_icu_bed_utilization	adult_icu_bed_utilization_coverage	adult_icu_bed_utilization_numerator	adult_icu_bed_utilization_denominator	geocoded_state	total_hospitals_surveyed	percentage_reporting_critical_staffing_shortage
LA	8/1/2020	43	142	37	45	139	38	12	221	14329	222	9283	222	42	4	181	221	42	221	1	220	2	220	1444	221	427	221	415	221	42	3	37	3	0	3	0	3	1970	220	0.647847024	222	9283	14329	0.203883495	4	42	206	0.130434783	4	42	322	0.214720812	220	423	1970	0.725380711	220	1429	1970	POINT (-91.840238 30.921314)	222	19.36936937
MA	8/1/2020	6	71	5	5	72	5	7	77	17280	82	12592	82	658	82	24	75	158	77	0	70	4	70	904	71	64	70	32	70	654	78	447	77	4	78	1	77	1870	71	0.728703704	82	12592	17280	0.0522554	82	658	12592	0.038078704	82	658	17280	0.034224599	70	64	1870	0.48342246	71	904	1870	POINT (-71.481104 42.151077)	82	7.317073171
MD	8/1/2020	3	41	9	3	41	9	8	43	9753	50	7509	52	1161	53	115	39	304	41	1	33	8	32	703	42	194	46	140	46	1138	49	629	47	16	49	4	47	1044	40	0.760791551	50	7420	9753	0.153682248	52	1154	7509	0.116374449	50	1135	9753	0.16781158	39	171	1019	0.665703276	39	691	1038	POINT (-76.687282 38.946466)	53	5.660377358
ME	8/1/2020	3	26	4	3	26	4	0	31	2916	32	1955	33	93	33	4	27	15	24	0	26	0	20	179	29	13	28	6	28	88	30	71	29	0	30	0	29	322	29	0.669410151	32	1952	2916	0.047570332	33	93	1955	0.031893004	32	93	2916	0.040752351	27	13	319	0.566455696	28	179	316	POINT (-69.06137 45.117911)	33	9.090909091
MI	8/1/2020	3	90	47	7	82	51	4	101	17266	128	13315	132	683	140	951	107	93	111	1	90	4	106	1133	100	133	121	101	118	505	124	292	123	16	124	13	123	1596	96	0.728425808	128	12577	17266	0.039879835	132	531	13315	0.029364068	128	507	17266	0.065162907	94	104	1596	0.70802005	94	1130	1596	POINT (-85.730977 44.874798)	140	2.142857143
MN	8/1/2020	6	76	44	8	74	44	2	122	10490	122	6770	125	392	126	16	104	52	105	0	104	4	105	498	106	82	107	66	107	296	114	194	112	12	114	7	112	751	96	0.64270734	122	6742	10490	0.057902511	125	392	6770	0.037368923	122	392	10490	0.10918775	96	82	751	0.663115846	96	498	751	POINT (-94.198308 46.349153)	126	4.761904762
MO	8/1/2020	22	64	47	27	58	48	8	114	18367	120	11491	124	1069	133	71	91	162	92	0	84	13	81	1013	92	286	104	243	103	998	120	746	112	32	121	11	113	1702	86	0.602493603	120	11066	18367	0.082064224	124	943	11491	0.051342081	120	943	18367	0.12183055	76	197	1617	0.58519389	84	996	1702	POINT (-92.477244 38.3675)	133	16.54135338
MS	8/1/2020	33	60	4	39	49	9	1	75	7091	92	5079	96	1544	97	101	65	90	56	1	50	2	50	521	58	375	59	352	57	1036	81	859	80	13	81	5	80	522	55	0.703708927	92	4990	7091	0.303799961	96	1543	5079	0.216753631	92	1537	7091	0.695652174	54	352	506	0.978927203	55	511	522	POINT (-89.65344 32.713378)	97	34.02061856
MT	8/1/2020	9	20	37	9	20	37	0	46	2215	62	1536	56	79	34	18	21	29	20	0	17	8	19	112	22	19	53	17	20	78	24	69	20	1	24	0	20	157	42	0.678853755	52	1374	2024	0.057833089	34	79	1366	0.046231792	30	73	1579	0.103896104	41	16	154	0.629370629	15	90	143	POINT (-109.645127 47.033521)	66	13.63636364

The data provides us with “critical_staffing_shortage_today_yes”, “critical_staffing_shortage_today_no”, and “critical_staffing_shortage_today_not_reported”. Using this information, we can calcuate the total number of hospitals in the state that the data is surveying.

If we use the following statement, we can see the results in a column labeled total_hospitals_surveyed.

SELECT critical_staffing_shortage_today_yes, 
    critical_staffing_shortage_today_no, 
    critical_staffing_shortage_today_not_reported,
    critical_staffing_shortage_today_yes+
    critical_staffing_shortage_today_no+
    critical_staffing_shortage_today_not_reported AS "total_hospitals_surveyed" FROM covid_table; 

The first ten rows of data for this call should look like the following:

critical_staffing_shortage_today_yes	critical_staffing_shortage_today_no	critical_staffing_shortage_today_not_reported	total_hospitals_surveyed
43	142	37	222
6	71	5	82
3	41	9	53
3	26	4	33
3	90	47	140
6	76	44	126
22	64	47	133
33	60	4	97
9	20	37	66
3	95	22	120
…

Now, here we are able to look at results but they are not stored. To store these values for future use, we can add a column to the table.

ALTER TABLE covid_table ADD total_hospitals_surveyed INTEGER;

Then, we can add the calculated data to the newly added column.

UPDATE covid_table SET total_hospitals_surveyed = 
    critical_staffing_shortage_today_yes+
    critical_staffing_shortage_today_no+
    critical_staffing_shortage_today_not_reported;

What if we want to look at the percentage of hospitals surveys that are recording a shortage? We can add another column to the table to store that value.

ALTER TABLE covid_table ADD percentage_reporting_critical_staffing_shortage FLOAT;
UPDATE covid_table SET percentage_reporting_critical_staffing_shortage = 
    CAST(critical_staffing_shortage_today_yes as FLOAT)/total_hospitals_surveyed * 100;
SELECT percentage_reporting_critical_staffing_shortage FROM covid_table; 

percentage_reporting_critical_staffing_shortage
19.36936937
7.317073171
5.660377358
9.090909091
2.142857143
4.761904762
16.54135338
34.02061856
13.63636364
2
…

Now that we have this data, we can look at states that have the highest perentages of critical staffing shortages on a given day. Specifically, we will look at the first

SELECT * FROM covid_table WHERE date="2020/08/01" ORDER by 
     percentage_reporting_critical_staffing_shortage DESC;

state	date	critical_staffing_shortage_today_yes	critical_staffing_shortage_today_no	critical_staffing_shortage_today_not_reported	critical_staffing_shortage_anticipated_within_week_yes	critical_staffing_shortage_anticipated_within_week_no	critical_staffing_shortage_anticipated_within_week_not_reported	hospital_onset_covid	hospital_onset_covid_coverage	inpatient_beds	inpatient_beds_coverage	inpatient_beds_used	inpatient_beds_used_coverage	inpatient_beds_used_covid	inpatient_beds_used_covid_coverage	previous_day_admission_adult_covid_confirmed	previous_day_admission_adult_covid_confirmed_coverage	previous_day_admission_adult_covid_suspected	previous_day_admission_adult_covid_suspected_coverage	previous_day_admission_pediatric_covid_confirmed	previous_day_admission_pediatric_covid_confirmed_coverage	previous_day_admission_pediatric_covid_suspected	previous_day_admission_pediatric_covid_suspected_coverage	staffed_adult_icu_bed_occupancy	staffed_adult_icu_bed_occupancy_coverage	staffed_icu_adult_patients_confirmed_and_suspected_covid	staffed_icu_adult_patients_confirmed_and_suspected_covid_coverage	staffed_icu_adult_patients_confirmed_covid	staffed_icu_adult_patients_confirmed_covid_coverage	total_adult_patients_hospitalized_confirmed_and_suspected_covid	total_adult_patients_hospitalized_confirmed_and_suspected_covid_coverage	total_adult_patients_hospitalized_confirmed_covid	total_adult_patients_hospitalized_confirmed_covid_coverage	total_pediatric_patients_hospitalized_confirmed_and_suspected_covid	total_pediatric_patients_hospitalized_confirmed_and_suspected_covid_coverage	total_pediatric_patients_hospitalized_confirmed_covid	total_pediatric_patients_hospitalized_confirmed_covid_coverage	total_staffed_adult_icu_beds	total_staffed_adult_icu_beds_coverage	inpatient_beds_utilization	inpatient_beds_utilization_coverage	inpatient_beds_utilization_numerator	inpatient_beds_utilization_denominator	percent_of_inpatients_with_covid	percent_of_inpatients_with_covid_coverage	percent_of_inpatients_with_covid_numerator	percent_of_inpatients_with_covid_denominator	inpatient_bed_covid_utilization	inpatient_bed_covid_utilization_coverage	inpatient_bed_covid_utilization_numerator	inpatient_bed_covid_utilization_denominator	adult_icu_bed_covid_utilization	adult_icu_bed_covid_utilization_coverage	adult_icu_bed_covid_utilization_numerator	adult_icu_bed_covid_utilization_denominator	adult_icu_bed_utilization	adult_icu_bed_utilization_coverage	adult_icu_bed_utilization_numerator	adult_icu_bed_utilization_denominator	geocoded_state	total_hospitals_surveyed	percentage_reporting_critical_staffing_shortage
SC	8/1/2020	44	21	14	45	20	14	12	62	10937	75	7982	78	1717	78	182	65	93	64	2	53	0	50	929	59	362	65	337	64	1713	74	1532	73	6	74	5	73	1200	57	0.708603822	75	7750	10937	0.215108995	78	1717	7982	0.155618543	75	1702	10937	0.275833333	57	331	1200	0.750833333	56	901	1200	POINT (-80.864614 33.878572)	79	55.69620253
VI	8/1/2020	1	1	0	1	1	0	0	2	168	2	81	2	7	2	0	2	0	2	0	2	0	2	8	2	6	2	6	2	7	2	7	2	0	2	0	2	20	2	0.482142857	2	81	168	0.086419753	2	7	81	0.041666667	2	7	168	0.3	2	6	20	0.4	2	8	20	POINT (-64.840836 18.060868)	2	50
MS	8/1/2020	33	60	4	39	49	9	1	75	7091	92	5079	96	1544	97	101	65	90	56	1	50	2	50	521	58	375	59	352	57	1036	81	859	80	13	81	5	80	522	55	0.703708927	92	4990	7091	0.303799961	96	1543	5079	0.216753631	92	1537	7091	0.695652174	54	352	506	0.978927203	55	511	522	POINT (-89.65344 32.713378)	97	34.02061856
AR	8/1/2020	28	45	17	29	42	19	21	76	7936	86	5147	89	1894	90	50	69	213	68	2	58	3	57	645	71	348	68	255	65	1782	84	680	80	111	84	8	80	897	65	0.646673387	86	5132	7936	0.367981348	89	1894	5147	0.238533266	86	1893	7936	0.318435754	60	285	895	0.705275229	64	615	872	POINT (-92.439162 34.899825)	90	31.11111111
GA	8/1/2020	42	51	48	43	47	51	815	106	13954	112	11260	114	4323	140	235	95	122	83	17	76	59	71	1125	80	688	88	611	86	2645	106	2068	104	73	106	17	104	1168	70	0.788118527	111	10958	13904	0.285599212	113	3191	11173	0.224870017	111	3114	13848	0.458549223	64	531	1158	0.908390411	70	1061	1168	POINT (-83.426574 32.638481)	141	29.78723404
AZ	8/1/2020	29	33	36	30	31	37	4	65	9595	75	9657	85	2479	87	124	72	212	59	2	52	9	48	977	55	625	62	603	62	2364	77	1978	85	27	77	13	75	983	45	0.832412715	75	7987	9595	0.256187222	85	2474	9657	0.221573736	75	2126	9595	0.434384537	43	427	983	0.83214649	45	818	983	POINT (-111.664616 34.293239)	98	29.59183673
NM	8/1/2020	6	15	2	6	15	2	0	19	1581	22	1450	23	143	22	17	18	8	15	0	13	0	13	163	18	38	17	27	17	139	20	114	20	4	20	0	20	230	14	0.618595825	22	978	1581	0.104915627	22	143	1363	0.073871409	21	108	1462	0.139130435	14	32	230	0.639130435	14	147	230	POINT (-106.108388 34.421369)	23	26.08695652
ND	8/1/2020	13	33	4	12	33	5	6	46	2266	47	1416	48	110	50	7	46	11	44	0	47	10	47	101	44	16	46	12	46	79	47	51	47	4	47	0	47	103	42	0.62180053	47	1409	2266	0.077683616	48	110	1416	0.048543689	47	110	2266	0.097087379	41	10	103	0.553398058	42	57	103	POINT (-100.46931 47.446305)	50	26
CA	8/1/2020	93	232	68	107	217	69	48	373	62613	377	44592	379	8097	393	714	330	734	334	13	288	32	288	5407	358	2068	359	1893	358	8025	384	6607	383	136	371	96	370	7279	354	0.710251865	377	44471	62613	0.17561446	379	7831	44592	0.125069874	377	7831	62613	0.281688205	351	2049	7274	0.741448001	354	5397	7279	POINT (-119.662127 37.211164)	393	23.66412214
TX	8/1/2020	118	323	93	141	290	103	134	522	55673	529	39733	533	10167	533	826	516	763	514	27	513	54	509	5385	510	3102	510	2909	508	10066	523	8716	521	97	523	74	521	6677	506	0.710021016	529	39529	55673	0.256263548	532	10167	39674	0.182840588	528	10161	55573	0.462646265	501	3084	6666	0.800958514	506	5348	6677	POINT (-99.317341 31.447238)	534	22.09737828
AL	8/1/2020	23	77	10	25	75	10	4	95	14787	109	10399	109	2072	105	119	62	70	65	4	92	10	91	1233	103	536	97	499	97	2025	103	1790	103	47	102	36	102	1439	103	0.703252857	109	10399	14787	0.201595641	104	2072	10278	0.141694591	104	2072	14623	0.37248089	96	536	1439	0.856845031	103	1233	1439	POINT (-86.844516 32.756889)	110	20.90909091
LA	8/1/2020	43	142	37	45	139	38	12	221	14329	222	9283	222	42	4	181	221	42	221	1	220	2	220	1444	221	427	221	415	221	42	3	37	3	0	3	0	3	1970	220	0.647847024	222	9283	14329	0.203883495	4	42	206	0.130434783	4	42	322	0.214720812	220	423	1970	0.725380711	220	1429	1970	POINT (-91.840238 30.921314)	222	19.36936937
OK	8/1/2020	25	47	73	33	38	74	4	74	8142	83	4904	83	724	137	50	58	60	57	0	49	1	50	634	65	204	63	194	63	712	131	578	127	9	80	2	77	1032	104	0.602309015	83	4904	8142	0.11358075	83	557	4904	0.06841071	83	557	8142	0.196261682	58	189	963	0.644240571	63	632	981	POINT (-97.508297 35.583449)	145	17.24137931
RI	8/1/2020	2	10	0	2	10	0	1	12	1754	11	1907	12	56	12	5	11	2	2	0	2	0	2	141	11	14	8	13	8	55	12	52	12	1	12	1	12	156	11	1.021664766	11	1792	1754	0.029365496	12	56	1907	0.031927024	11	56	1754	0.093959732	8	14	149	0.903846154	11	141	156	POINT (-71.534637 41.572574)	12	16.66666667
MO	8/1/2020	22	64	47	27	58	48	8	114	18367	120	11491	124	1069	133	71	91	162	92	0	84	13	81	1013	92	286	104	243	103	998	120	746	112	32	121	11	113	1702	86	0.602493603	120	11066	18367	0.082064224	124	943	11491	0.051342081	120	943	18367	0.12183055	76	197	1617	0.58519389	84	996	1702	POINT (-92.477244 38.3675)	133	16.54135338
TN	8/1/2020	19	83	22	17	85	22	8	100	19762	124	11058	116	1743	114	135	109	197	109	3	99	4	100	1521	100	458	99	392	99	1721	112	1430	112	20	112	10	112	2394	108	0.604691858	116	11058	18287	0.157234432	109	1717	10920	0.094466425	114	1743	18451	0.200877193	98	458	2280	0.679321126	100	1521	2239	POINT (-86.343226 35.842998)	124	15.32258065
KS	8/1/2020	19	86	21	26	75	25	7	116	7082	116	4236	122	515	126	38	91	74	83	0	76	3	71	473	65	85	75	77	73	470	95	343	89	13	95	6	89	624	59	0.567918667	116	4022	7082	0.105524079	122	447	4236	0.058881672	116	417	7082	0.105863192	55	65	614	0.764802632	56	465	608	POINT (-98.38018 38.484729)	126	15.07936508
MT	8/1/2020	9	20	37	9	20	37	0	46	2215	62	1536	56	79	34	18	21	29	20	0	17	8	19	112	22	19	53	17	20	78	24	69	20	1	24	0	20	157	42	0.678853755	52	1374	2024	0.057833089	34	79	1366	0.046231792	30	73	1579	0.103896104	41	16	154	0.629370629	15	90	143	POINT (-109.645127 47.033521)	66	13.63636364
NV	8/1/2020	8	53	1	9	52	1	35	61	8552	62	6251	62	1271	62	75	61	118	61	1	60	1	60	679	60	344	59	316	59	1225	61	1034	61	2	61	8	61	838	60	0.730940131	62	6251	8552	0.203327468	62	1271	6251	0.148620206	62	1271	8552	0.410501193	59	344	838	0.81026253	60	679	838	POINT (-114.916992 39.283294)	62	12.90322581
NJ	8/1/2020	10	55	13	10	53	15	20	57	14453	67	10744	69	1144	78	41	59	194	56	0	51	11	52	878	57	100	61	60	58	1035	69	552	69	73	70	30	70	1623	57	0.710648308	67	10271	14453	0.098752792	69	1061	10744	0.070020065	67	1012	14453	0.059042884	54	95	1609	0.536660505	56	871	1623	POINT (-74.655514 40.110253)	78	12.82051282
ID	8/1/2020	6	36	5	13	29	5	2	40	3997	45	1842	41	233	45	13	24	2	25	0	22	0	23	192	36	43	40	42	39	233	42	227	41	0	41	0	41	270	34	0.481316958	41	1842	3827	0.127332602	39	232	1822	0.060067028	43	233	3879	0.148148148	33	40	270	0.666666667	30	180	270	POINT (-114.659366 44.389073)	47	12.76595745
PA	8/1/2020	24	155	17	25	144	27	12	141	25174	166	19790	183	1593	196	91	155	314	152	7	126	19	126	2253	140	266	151	206	147	1432	168	952	162	62	169	31	163	2844	127	0.732541511	166	18441	25174	0.070843861	183	1402	19790	0.053666481	166	1351	25174	0.08520841	117	231	2711	0.748855231	122	2126	2839	POINT (-77.838886 40.896694)	196	12.24489796
VA	8/1/2020	11	41	40	12	39	41	22	78	15341	84	10807	86	1503	92	48	67	123	62	1	58	3	55	744	66	190	67	158	67	1186	75	901	74	55	80	38	79	1401	65	0.69858549	84	10717	15341	0.133524567	86	1443	10807	0.093670556	84	1437	15341	0.126825519	61	165	1301	0.559969442	61	733	1309	POINT (-78.666382 37.510861)	92	11.95652174
WA	8/1/2020	10	68	17	14	63	18	4	84	9037	79	8310	93	560	95	29	66	83	72	0	65	2	67	748	79	124	81	108	80	501	86	408	84	26	86	2	83	874	64	0.614141861	79	5550	9037	0.066907341	93	556	8310	0.043155915	79	390	9037	0.093317972	58	81	868	0.598398169	62	523	874	POINT (-120.592492 47.411715)	95	10.52631579
PR	8/1/2020	7	38	22	7	36	24	8	65	8440	67	4236	67	571	66	8	67	29	65	1	64	11	63	429	65	66	64	40	64	538	66	225	66	33	66	2	66	686	65	0.501895735	67	4236	8440	0.137557215	66	571	4151	0.068506299	66	571	8335	0.096774194	64	66	682	0.625364431	65	429	686	POINT (-66.414655 18.215692)	67	10.44776119
WY	8/1/2020	3	19	7	2	19	8	3	29	1694	29	676	29	46	29	14	27	19	28	0	27	2	28	58	28	16	29	14	29	44	29	23	29	2	29	0	29	104	28	0.39905549	29	676	1694	0.068047337	29	46	676	0.027154664	29	46	1694	0.153846154	28	16	104	0.557692308	28	58	104	POINT (-107.55145 42.999627)	29	10.34482759
NH	8/1/2020	3	23	4	6	20	4	3	29	3082	30	2075	30	112	30	5	26	31	28	0	24	0	24	160	25	23	21	13	21	111	24	67	23	1	24	0	23	234	21	0.673264114	30	2075	3082	0.053975904	30	112	2075	0.036340039	30	112	3082	0.092511013	19	21	227	0.61965812	20	145	234	POINT (-71.57139 43.680429)	30	10
WV	8/1/2020	6	46	8	9	43	8	2	55	6565	59	4456	59	424	60	8	47	41	50	0	37	1	37	471	52	65	54	51	52	422	59	341	57	2	59	1	57	654	52	0.678750952	59	4456	6565	0.095152603	59	424	4456	0.06458492	59	424	6565	0.099388379	52	65	654	0.720183486	52	471	654	POINT (-80.613729 38.642579)	60	10
UT	8/1/2020	5	35	12	7	33	12	3	51	5303	51	3191	52	347	52	21	45	55	40	3	39	4	36	356	43	85	40	78	40	313	46	230	46	23	46	17	46	553	38	0.583820479	51	3096	5303	0.108743341	52	347	3191	0.063737507	51	338	5303	0.150090416	37	83	553	0.613019892	38	339	553	POINT (-111.678222 39.323779)	52	9.615384615
ME	8/1/2020	3	26	4	3	26	4	0	31	2916	32	1955	33	93	33	4	27	15	24	0	26	0	20	179	29	13	28	6	28	88	30	71	29	0	30	0	29	322	29	0.669410151	32	1952	2916	0.047570332	33	93	1955	0.031893004	32	93	2916	0.040752351	27	13	319	0.566455696	28	179	316	POINT (-69.06137 45.117911)	33	9.090909091
IN	8/1/2020	12	109	12	17	103	13	1	117	15538	129	9415	129	962	133	51	103	119	102	0	97	2	98	1338	121	306	118	300	117	929	121	545	120	16	121	1	120	2188	123	0.60593384	129	9415	15538	0.09803505	129	923	9415	0.059402755	129	923	15538	0.143057504	117	306	2139	0.635629454	121	1338	2105	POINT (-86.2818 39.919991)	133	9.022556391
AK	8/1/2020	2	5	17	2	5	17	0	14	1339	22	921	24	51	24	4	22	12	21	1	22	1	21	67	12	8	22	3	22	50	22	26	22	1	22	1	22	43	7	0.429424944	22	575	1339	0.055374593	24	51	921	0.024645258	22	33	1339	0.093023256	6	4	43	0.558139535	7	24	43	POINT (-151.631889 63.631126)	24	8.333333333
DE	8/1/2020	1	9	2	1	8	3	0	10	2451	11	1830	12	184	12	2	6	2	6	0	4	0	5	38	5	12	6	10	6	166	7	138	7	8	7	8	7	47	4	0.709914321	11	1740	2451	0.100546448	12	184	1830	0.049775602	11	122	2451	0.085106383	4	4	47	0.808510638	4	38	47	POINT (-75.452394 38.994926)	12	8.333333333
IL	8/1/2020	15	137	31	18	118	47	2	171	27240	167	17121	169	1605	183	100	126	268	127	5	110	17	112	1696	120	372	136	266	133	1437	158	810	151	51	158	20	151	2824	102	0.624068472	166	16916	27106	0.089539162	169	1533	17121	0.056020558	167	1526	27240	0.107780157	98	302	2802	0.538571429	99	1508	2800	POINT (-89.148632 40.124144)	183	8.196721311
MA	8/1/2020	6	71	5	5	72	5	7	77	17280	82	12592	82	658	82	24	75	158	77	0	70	4	70	904	71	64	70	32	70	654	78	447	77	4	78	1	77	1870	71	0.728703704	82	12592	17280	0.0522554	82	658	12592	0.038078704	82	658	17280	0.034224599	70	64	1870	0.48342246	71	904	1870	POINT (-71.481104 42.151077)	82	7.317073171
VT	8/1/2020	1	11	3	1	11	3	0	14	570	12	370	12	10	15	1	8	13	9	0	7	1	7	28	6	1	8	1	8	8	9	2	8	1	9	0	8	48	5	0.649122807	12	370	570	0.027027027	12	10	370	0.01754386	12	10	570	0.020833333	5	1	48	0.583333333	5	28	48	POINT (-72.662695 44.075252)	15	6.666666667
MD	8/1/2020	3	41	9	3	41	9	8	43	9753	50	7509	52	1161	53	115	39	304	41	1	33	8	32	703	42	194	46	140	46	1138	49	629	47	16	49	4	47	1044	40	0.760791551	50	7420	9753	0.153682248	52	1154	7509	0.116374449	50	1135	9753	0.16781158	39	171	1019	0.665703276	39	691	1038	POINT (-76.687282 38.946466)	53	5.660377358
NY	8/1/2020	9	106	44	7	106	46	25	135	40550	146	29330	148	1857	159	73	124	215	120	1	113	12	107	2422	117	240	119	110	116	1582	140	632	130	65	140	18	130	4493	109	0.678932	145	27437	40412	0.05980225	148	1754	29330	0.041874229	146	1698	40550	0.044922789	99	192	4274	0.521331249	106	2334	4477	POINT (-75.59655 42.921241)	159	5.660377358
OH	8/1/2020	11	83	106	11	82	107	14	72	30331	200	20144	200	1871	199	72	88	124	86	1	67	45	67	2503	183	443	180	331	180	1874	187	1175	187	32	187	5	187	4098	163	0.664139	200	20144	30331	0.093117006	199	1871	20093	0.061906495	199	1871	30223	0.108418992	160	443	4086	0.608833577	163	2495	4098	POINT (-82.70936 40.415534)	200	5.5
MN	8/1/2020	6	76	44	8	74	44	2	122	10490	122	6770	125	392	126	16	104	52	105	0	104	4	105	498	106	82	107	66	107	296	114	194	112	12	114	7	112	751	96	0.64270734	122	6742	10490	0.057902511	125	392	6770	0.037368923	122	392	10490	0.10918775	96	82	751	0.663115846	96	498	751	POINT (-94.198308 46.349153)	126	4.761904762
WI	8/1/2020	6	44	83	6	30	97	0	127	11526	128	7067	128	612	133	72	119	259	117	1	116	3	116	799	122	160	126	127	126	608	127	380	127	3	127	1	127	1536	121	0.613284327	127	7008	11427	0.085467667	128	604	7067	0.052316502	128	603	11526	0.104166667	121	160	1536	0.51953125	121	798	1536	POINT (-89.732969 44.639954)	133	4.511278195
NE	8/1/2020	4	5	93	4	5	93	0	48	1538	49	891	49	113	65	0	5	0	4	0	45	0	3	84	36	1	3	29	55	113	65	98	61	0	8	0	4	173	42	0.589333333	47	884	1500	0.057012543	47	50	877	0.032916392	48	50	1519	0.0625	3	1	16	0.56	36	84	150	POINT (-99.810595 41.527099)	102	3.921568627
SD	8/1/2020	2	41	10	6	36	11	0	49	2214	51	1283	52	60	53	3	37	9	39	0	33	4	36	64	36	7	31	6	31	42	41	36	37	18	41	13	37	93	27	0.575880759	51	1275	2214	0.046765394	52	60	1283	0.027100271	51	60	2214	0.021505376	24	2	93	0.47311828	27	44	93	POINT (-100.230491 44.43614)	53	3.773584906
FL	8/1/2020	9	21	229	8	21	230	218	223	73299	257	41150	256	10143	259	717	239	450	240	8	225	28	223	4676	231	2222	242	2011	242	9510	256	8851	256	633	252	60	255	5710	230	0.574752779	256	41150	71596	0.241385176	256	9933	41150	0.13821471	257	10131	73299	0.35374868	227	2010	5682	0.815236427	230	4655	5710	POINT (-82.420978 28.260731)	259	3.474903475
OR	8/1/2020	2	37	20	2	36	21	1	53	4124	47	3766	55	273	59	20	44	118	42	0	39	6	36	352	43	56	48	41	47	255	52	138	49	5	52	1	49	434	33	0.690349176	47	2847	4124	0.068773234	55	259	3766	0.048254122	47	199	4124	0.058139535	32	25	430	0.599078341	33	260	434	POINT (-120.605279 43.940449)	59	3.389830508
IA	8/1/2020	4	120	0	3	121	0	12	124	8557	124	175	3	336	124	35	124	47	124	0	124	2	124	440	124	92	124	90	124	327	124	243	124	9	124	4	124	737	124	0.634057971	3	175	276	0.074285714	3	13	175	0.039266098	124	336	8557	0.124830393	124	92	737	0.597014925	124	440	737	POINT (-93.500061 42.074659)	124	3.225806452
CO	8/1/2020	3	78	14	5	72	18	6	89	9008	78	5749	81	494	95	41	78	69	68	0	73	6	62	553	63	117	74	91	72	422	85	331	79	70	85	57	79	952	57	0.624888988	78	5629	9008	0.077752653	81	447	5749	0.049067496	78	442	9008	0.094781683	52	89	939	0.599118943	55	544	908	POINT (-105.547832 38.998552)	95	3.157894737
NC	8/1/2020	3	95	22	14	87	19	5	76	21141	118	17135	119	1306	119	157	114	409	113	5	83	9	84	1729	112	379	113	354	112	1275	116	900	116	31	116	12	116	2420	112	0.800813585	118	16930	21141	0.076248392	118	1304	17102	0.061588023	117	1300	21108	0.155785124	112	377	2420	0.71446281	112	1729	2420	POINT (-79.130883 35.52603)	120	2.5
MI	8/1/2020	3	90	47	7	82	51	4	101	17266	128	13315	132	683	140	951	107	93	111	1	90	4	106	1133	100	133	121	101	118	505	124	292	123	16	124	13	123	1596	96	0.728425808	128	12577	17266	0.039879835	132	531	13315	0.029364068	128	507	17266	0.065162907	94	104	1596	0.70802005	94	1130	1596	POINT (-85.730977 44.874798)	140	2.142857143
KY	8/1/2020	1	17	91	4	91	14	7	101	11782	99	7580	103	863	109	155	95	138	95	1	92	14	91	943	88	548	90	134	88	776	96	522	96	25	96	2	96	1383	91	0.636309625	99	7497	11782	0.105672823	103	801	7580	0.067985062	99	801	11782	0.400877835	89	548	1367	0.681851048	87	943	1383	POINT (-85.290484 37.526683)	109	0.917431193
CT	8/1/2020	0	6	25	0	6	25	3	31	8133	30	5888	30	169	31	11	29	103	29	0	28	1	28	502	28	18	29	9	29	168	31	74	31	1	31	0	31	1038	27	0.723964097	30	5888	8133	0.028702446	30	169	5888	0.02077954	30	169	8133	0.01734104	27	18	1038	0.476878613	27	495	1038	POINT (-72.738288 41.575155)	31	0
DC	8/1/2020	0	10	1	0	10	1	1	10	2721	10	2034	10	188	11	16	11	75	11	1	11	14	11	248	11	18	10	18	10	162	11	99	11	26	11	8	11	404	11	0.747519294	10	2034	2721	0.09193707	10	187	2034	0.068724734	10	187	2721	0.044554455	10	18	404	0.613861386	11	248	404	POINT (-77.016275 38.904785)	11	0
HI	8/1/2020	0	11	19	0	11	19	0	12	1625	12	1726	26	70	16	11	23	14	23	0	8	0	8	43	9	15	11	14	11	70	15	67	15	0	15	0	15	67	9	0.677538462	12	1101	1625	0.047229791	12	52	1101	0.032	12	52	1625	0.059701493	9	4	67	0.641791045	9	43	67	POINT (-157.854309 21.361013)	30	0

By looking at this, we are able to tell which states have the largest percentage of staffing shortages accross the country on a given day. This is just one example of a way you can use SQL to further analyze data!