Home Forums SQL Server 2008 T-SQL (SS2K8) Not getting results expected from a Group by query RE: Not getting results expected from a Group by query

  • 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.