SQL Reporting Services, filtering on Field not in DISTINCT SELECT statement?

Question

I am using MS SQL Report Builder 3.0 to generate a report for SQL Reporting Services. I have a dataset that includes the columns AppointmentDate, PatientID, and InsuranceCarrier. I want to find out the number of DISTINCT patients that visited within a certain timeframe (begining AppointmentDate to ending AppointmentDate). The only time a patient should be present more than once in the query is if they had a different insurance carrier on a repeat visit.

If I perform the following query:

SELECT DISTINCT AppointmentDate, PaientID, InsuranceCarrier 
FROM Encounters
WHERE AppointmentDate >= @beginningofdaterange
AND AppointmentDate <= @endofdaterange

Then I get a list of each patient and insurance carrier that visited in that date range. Unfortunately, since each AppointmentDate is also distinct, the Patients get duplicated for each individual appointment date. For example, if Patient X shows up twice in that timeframe specified, it will show both appointments.

If I remove the AppointmentDate from my SELECT statement, then under Dataset properties in the Report Builder 3.0 I can no longer Filter based on the AppointmentDate expression. I could do my filtering directly in the WHERE clause of the T-SQL statement, but that means I cannot use the run-time Report Parameters input by the user. This is a problem because I am filtering based on Report Parameters that the user selects when running the report. They input the starting AppointmentDate and a "18-months prior" Parameter is calculated as the beginning and end AppointmentDates to filter against.

So how do I include the AppointmentDate so that I can filter with it, but not include it in my DISTINCT SELECT so that it properly DISTINCTifies my data.

Answer 1

@FreefallGeek,

What do you mean by you can't filter AppointmentDate if you remove it from SELECT? Report Builder allows you do dataset filtering based on the user assigned parameter in run time with query like this,

SELECT DISTINCT PaientID, InsuranceCarrier 
FROM Encounters
WHERE 
  AppointmentDate >= @beginningofdaterange
  AND AppointmentDate <= @endofdaterange

With @beginningofdaterange and @endofdaterange as your report parameter. This should work unless you need to do additional filtering that require to return AppointmentDate as result.

If you really need to return the Appointment date as result or for additional filtering, then the next question is what should be the AppointmentDate when there are multiple visit with same patient and insurance carrier? The first visit or the last visit within the date range? If that is the case, you could use group by like this for first visit,

SELECT Min(AppointmentDate) AS FirstAppointmentDate, PaientID, InsuranceCarrier 
FROM Encounters
WHERE 
  AppointmentDate >= @beginningofdaterange
  AND AppointmentDate <= @endofdaterange
GROUP BY PaientID, InsuranceCarrier
ORDER BY AppointmentDate

However, from your description, it appears that you only need the distinct patient and insurance carrier with the capability to filter the date. If that understanding is correct, you could just filter the appointment with user input parameter in the WHERE clause without the SELECT.