CODEWITHSUNDEEP
sqlsql-servert-sqlsubquery
Nathan St. Juliana
Nathan St. Juliana

Reputation: 13

SQL sub select returning multiple values

I want to count future Appointments made on the same day of an active appointment by Location. I expect multiple counts per Patient_ID given a date range. I am not sure if I need a temp table or if a subquery would work.

From the code below this is the error I get:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Definitions:

SQL

SELECT
  loc.Description
 ,Count(app.Appointment_ID)

 FROM [Ntier_HARH].[PM].[Appointments] app

 join [Ntier_HARH].[PM].[Resources] res 
    on res.Resource_ID = app.Resource_ID
 join [Ntier_HARH].[PM].[Practitioners] doc 
    on doc.Practitioner_ID = res.Practitioner_ID
 join [Ntier_HARH].[PM].[Scheduling_Locations] loc 
    on loc.Scheduling_Location_ID = app.Scheduling_Location_ID

 where      
   cast(app.DateTime_Scheduled as date) = '2017-01-16' 
     and app.status <> 'X'
     and cast(app.Appointment_DateTime as date) = 
       (Select cast(DateTime_Scheduled as date) 
        from [Ntier_HARH].[PM].[Appointments] 
        where Patient_ID = app.Patient_ID)

 group by loc.Description

Upvotes: 1

Views: 80

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

Don't you also need to group by the PatientId? If you want the count of appointments by location only, then the subquery isn't necessary. I don't see why the other two tables are necessary either.

SELECT l.Description, Count(a.Appointment_ID)

FROM [Ntier_HARH].[PM].[Appointments] a
   join [Ntier_HARH].[PM].[Scheduling_Locations] l 
      on l.Scheduling_Location_ID = a.Scheduling_Location_ID

where cast(a.DateTime_Scheduled as date) = '2017-01-16' 
   and a.status <> 'X'

group by l.Description

Upvotes: 0

nacho
nacho

Reputation: 5397

You may use in instead of =

where 

 cast(app.DateTime_Scheduled as date) = '2017-01-16' 
 and app.status <> 'X'
 and cast(app.Appointment_DateTime as date) IN (Select cast(DateTime_Scheduled as date) from [Ntier_HARH].[PM].[Appointments] where Patient_ID = app.Patient_ID)

 group by loc.Description

Upvotes: 1

Related Questions