Here are the DDL for the tables
====Patients====
[PatientID] [varchar](20) NOT NULL, --Primary Key
[LastName] [nvarchar](25) NULL,
[FirstName] [nvarchar](25) NULL,
[AKA] [varchar](50) NULL,
[ClinicianId] [int] NULL,
[MEDICAID_ID] [nvarchar](8) NULL,
[PendingMedicaid] [tinyint] NULL,
[PrivatePatient] [tinyint] NULL,
[MedicaidEffectiveDate] [datetime] NULL,
[MedicaidTermDate] [datetime] NULL,
[DOB] [datetime] NULL,
[SEX] [char](1) NULL,
[SSN] [varchar](12) NULL,
[ActiveClient] [smallint] NULL,
[HomelessShelter] smallint null
Remaining fields left out
===APGAttendance====
[PatientId] [varchar](20) NOT NULL, --Primary
[AttendDate] [datetime] NOT NULL, --Primary
[CTPCodes] [varchar](10) NOT NULL, --Primary
[CheckinBySecurty] [bit] NULL,
[SecurityTime] [varchar](12) NULL,
[GroupTime] [varchar](12) NULL,
[LocationID] [int] NULL,
[ClinicianID] [int] NULL,
Remaining fields left out to save space
CREATE TABLE [dbo].[Shelters](
[ShelterId] [int] IDENTITY(1,1) NOT NULL, --FOREIGN key to HomelessShelter
[ShelterName] [varchar](50) NULL,
[ShelterLocation] [varchar](100) NULL,
[SortOrder] [tinyint] NULL,
[isFreedomRes] [bit] NULL,
CONSTRAINT [PK_Shelters] PRIMARY KEY CLUSTERED
(
[ShelterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here is the data for Shelter table:
ShelterId Sheltername
----------- --------------------------------------------------
1 Freedom Residence I
2 Freedom Residence II
3 Freedom Residence III
4 Freedom Residence IV
5 Freedom Residence V
6 Freedom Residence VI
7 Freedom Residence VII
8 New Sight to Life
9 Jastian Management
10 Freedom Residence VIII
11 Freedom Residence VIIII
12 Joining Hands
13 Jireh Place
14 #1 Marketing/RYB/Top of the Hob
15 Uplifting Men
16 No Matter What/Rockaway
17 Forever USA
18 Well Being/WINS Enterprise
19 Steps to Success
20 Freedom Residence X
21 Freedom Residence XI
22 Freedom Residence XII
23 Freedom Residence XVI
24 Liberation
25 Freedom Residence XVII
26 Freedom Residence XVIII
27 Freedom Residence XVIIII
28 Freedom Residence XX
29 Freedom Residence XXI
30 Freedom Residence XXII
31 Freedom Residence XIII
32 Freedom Residence XIIII
33 Freedom Residence XV
The Results from the Query in question.
NarcoSite Attendcount ShelterName PatientCount
------------ ----------- --------------------------- ------------
Independance 120 Freedom Residence I 10
Independance 212 Freedom Residence II 27
Independance 250 Freedom Residence III 37
Independance 96 Freedom Residence IV 16
Independance 165 Freedom Residence V 21
Independance 170 Freedom Residence VI 19
Independance 320 Freedom Residence VIII 41
Independance 156 Freedom Residence XI 13
Independance 193 Freedom Residence XII 24
Independance 319 Freedom Residence XVI 38
Independance 58 Freedom Residence XVIII 0 <--Row in question
Independance 352 Freedom Residence XVIIII 46
Independance 14 Freedom Residence XIIII 1
BWT, I'm currently have the field Sitename in the query to distinguish which site the data is coming from and will be used in a report the data is feeding. This is part of a Union all query that retrieves the data from several servers.
In reference to the row in question, I did check the patient table and know that there are no records that have the HomelessShelter field set to 26. Not sure why it would display 58 as the count.
I did try and change around the join clause with a left join instead of an outer join. Pretty much the same results though.
I understand that the subquery may not be the best way to get the Patient Count, but was not sure of a better way to get this info without using a function, which would have to use the same type query to get the count anyways, so for now I had it within the parent query. If there is a better way, I will be happy to do it.
Thanks for the suggestions so far.