SQL sub select returning multiple values

Question

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:

Appointment_DateTime - (Date) is the actual appointment event
DateTime_Scheduled - (Date) is the logging timestamp of future appointments
Description - (text) is the Location Description
Patient_ID - (int) is the unique patient ID
Appointment_ID - (int) is the unique Appointment ID

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

Looking at the query, I believe you need min or max aggregate in sub query or you can use Row_Number to avoid sub-query — Pரதீப்
– Pரதீப், Commented Oct 19, 2017 at 14:04

nacho · Accepted Answer · 2017-10-19 13:59:27Z

1

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

answered Oct 19, 2017 at 13:59

nacho

5,3772 gold badges28 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nathan St. Juliana Over a year ago

That was it! Boy was that simple.

Charles Bretana · Accepted Answer · 2017-10-19 14:19:13Z

0

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

answered Oct 19, 2017 at 14:19

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

1 Comment

Nathan St. Juliana Over a year ago

I only wanted to count Appintment_ID if the Dates matched on a per patient_ID and not any date that matched.

Collectives™ on Stack Overflow

SQL sub select returning multiple values

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related