I have a list of al 51 states. I need a list of all states with a count of patients per state. If there were no patients in a state; I need the count to be listed as 0. The only way I know to do this is with a left join, but when I add my where critera; I'm losing rows.
Here is my query. I can't figure out why I'm losing all 51 states. If I comment out the where clause; it returns a count for all my data. that is not what I need. I need the counts for the date range and the other criteria I included.
Any help is much appreciated!
DECLARE @StartDate datetime = '01/01/2021', @EndDate datetime = '12/31/2021'
select
s.Abbr as State,
count(accession_no) as [Total Patients]
from [dbo].[States] s
left outer join ARKPPDB.Powerpath.dbo.vw_patient_2 p2 on s.Abbr = p2.home_state
left outer join ARKPPDB.Powerpath.dbo.accession_2 a on a.patient_id = p2.id
where a.created_date >= @StartDate and a.created_date <= @EndDate
and LEFT(a.accession_no, 2) not in ('CV')
and p2.home_postal_code is not null
group by s.Abbr
order by s.Abbr
Here are the results from the query: (49 rows) instead of 51 rows. State Total Patients AK 14 AL 504 AR 1023 AZ 756 CA 84 CO 788 CT 38 DC 3 DE 1 FL 2770 GA 1184 HI 63 IA 250 ID 402 IL 939 IN 340 KS 379 KY 81 LA 664 MA 1 MD 92 ME 42 MI 1092 MN 18 MO 1052 MS 656 MT 76 NC 173 ND 1 NE 8 NH 1 NJ 85 NM 275 NV 405 NY 10 OH 451 OK 503 OR 401 PA 801 SC 791 SD 107 TN 814 TX 1058 UT 20 VA 298 WA 254 WI 186 WV 184 WY 15
Here is a list of all rows from the states table: 51 rows ID Abbr Name 1 AL Alabama 2 AK Alaska 3 AZ Arizona 4 AR Arkansas 5 CA California 6 CO Colorado 7 CT Connecticut 8 DE Delaware 9 DC District Of Columbia 10 FL Florida 11 GA Georgia 12 HI Hawaii 13 ID Idaho 14 IL Illinois 15 IN Indiana 16 IA Iowa 17 KS Kansas 18 KY Kentucky 19 LA Louisiana 20 ME Maine 21 MD Maryland 22 MA Massachusetts 23 MI Michigan 24 MN Minnesota 25 MS Mississippi 26 MO Missouri 27 MT Montana 28 NE Nebraska 29 NV Nevada 30 NH New Hampshire 31 NJ New Jersey 32 NM New Mexico 33 NY New York 34 NC North Carolina 35 ND North Dakota 36 OH Ohio 37 OK Oklahoma 38 OR Oregon 39 PA Pennsylvania 40 RI Rhode Island 41 SC South Carolina 42 SD South Dakota 43 TN Tennessee 44 TX Texas 45 UT Utah 46 VT Vermont 47 VA Virginia 48 WA Washington 49 WV West Virginia 50 WI Wisconsin 51 WY Wyoming