quote:

---

Originally posted by andros30

I'm trying to get an average patient seen over the course of a year and I'm wondering how or if it is possible to return the data as one report. Here is my query:

select OfficeNumber, Entity, [Day], avg([Consults Only]) as ConsOnly, avg([Without Consults]) as WithoutCons, avg([Total]) as Total,Count(Day)as Countofdays
from (
select a.OfficeNumber, c.ConfigECode as Entity, month(a.dt_appt) as [Month], day(a.dt_appt) as [Day],
sum(case when a.task_cd in (2,24,902) then 1 else 0 end) as 'Consults Only',
sum(case when a.task_cd not in (2,24,902) then 1 else 0 end) as 'Without Consults',
count(a.task_cd) as 'Total'
from tbl_appt a with(nolock)
inner join tConfig c
on c.ConfigOfficeNumber = a.OfficeNumber
where a.OfficeNumber in ('050770','050732','050733')
and a.dt_appt >= '01/01/08'
and a.stat_cd in (1,7)
group by a.OfficeNumber, c.ConfigECode,month(a.dt_appt), day(a.dt_appt)
) x
group by OfficeNumber, Entity, [Day]
order by Entity, [Day]

The query above gives me the averages I want by the month. But I'd like to be able to show the daily counts which is in the nested select. Is this possible?

---