Average # of cases based on count of days > 6 day, 7 days, 8 days

1

I need to come up with a report that can take the average # of cases for physicians that have cases assigned more than 6 days, 7 days, and 8 days.

Here is my query to bring the data into SSRS:
select a.accession_no, a.created_date, p.last_name, p.first_name, p.mid_name, p.full_name
from accession_2 a
join acc_role_assignment r on a.id = r.acc_id
join personnel_2 p on p.id = r.assigned_to_id and role_id = 2
where created_date >= @StartDate and created_date < = @EndDate

I'm not sure how to do this in my report. I can group on created date, then on physician to get the # of cases per day and per physician, but I'm not sure how to do the above, can someone give me some tips and ideas on how to get started?

2

So I guess "accession_no" represents a "case"?? You have to remember that we know nothing about your data.

If I understand the requirement correctly, maybe?:


select COUNT(*) / COUNT(DISTINCT r.assigned_to_id) AS avg_cases_per_physician
    --a.created_date, p.last_name, p.first_name, p.mid_name, p.full_name
from accession_2 a
join acc_role_assignment r on a.id = r.acc_id
--join personnel_2 p on p.id = r.assigned_to_id and role_id = 2
where a.created_date >= @StartDate and a.created_date < = @EndDate
3

What you just gave me was the average # of cases per full_name. What I need is the average # of cases by date where the name had cases > 6, 7, 8

Data looks like this.

Created_Date Full_name Accession# (case#)
01/01/2022 10:00 john smith 12344
01/01/2022 11:00 john smith 12445
01/01/2022 1200 john smith 12589
01/01/2022 11:00 jane doe 3953
01/01/2022 12:00 jane doe 3955
01/02/2022 13:00 jane doe 939394

etc.

I have SSRS if it's easier to come up with this.