SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 AVG(DATEDIFF(d,table1.date1, MIN(table2.date2))
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

moorzee
Starting Member

United Kingdom
5 Posts

Posted - 04/22/2013 :  17:50:19  Show Profile  Reply with Quote
I am trying to create a report to provide data from a SQL Server database. I have 3 tables I am interested in, Client, Referral, Appointment. A client can have 1.* referrals and a referral can have 0.* appointments booked against it.

In my report I want to show the average time from a referral being received to 1st appointment.

Am trying the above in my stored procedure but receive "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

Is there a slick way of getting this to work without sub query you can think of?

Table structure below.

Client ClientID INT IIDENTITY (pk) Forename VARCHAR(50) Surname VARCHAR(50) DOB DATETIME

Referral ReferralID INT IDENTITY (pk) ClientID INT (fk) ReferralRequestReceivedDate DATETIME OrgaisationAreaId INT (fk)

Appointment AppintmentID INT IDENTITY (pk) ReferralId INT(fk) AppointmentDate DATETIME AttendanceTypeId INT (fk) AppointmentTypeID INT (fk)

AttendanceTypes AttendanceTypeID INT IDENTITY (pk) Name VARCHAR(50)

AppointmentTypes AppointmentTypeID INT IDENTITY (pk) Name VARCHAR(50)

OrganisationArea OrgaisationAreaId INT IDENTITY(pk) Name VARCHAR(50)

My existing proc has counts for appointments attended by age and attendance type as below...

SELECT OA.Name,
COUNT(CASE WHEN AppointmentTypeId IN(1,3)
AND AppointmentDate BETWEEN '27 Jan 2013' AND '13 Apr 2013' THEN AppointmentId END) AS AppsBooked,
AVG(DATEDIFF(d, ReferralRequestReceivedDate, MIN(A.AppointmentDate))) AS AvgAllocationWaitTime
FROM OrganisationAreas OA
LEFT OUTER JOIN Clients C
ON OA.OrganisationAreaId = C.OrganisationAreaId
LEFT OUTER JOIN IaptReferrals R
ON C.ClientId = R.ClientId
LEFT OUTER JOIN IaptAppointments A
ON R.IaptReferralId = A.Referral_IaptReferralId
GROUP BY OA.OrganisationAreaId, OA.Name

Edited by - moorzee on 04/22/2013 17:53:30

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 04/22/2013 :  18:13:04  Show Profile  Reply with Quote
SQL Server does not allow you to use nested aggregates in the same group. One possible workaround is using the OVER() clause that I am showing below.

When you run the query below, it is probably not going to give you what you are looking for. If it does not, remove one or the other or both of the partition by clauses. (If you removed both partition by clauses, it would be just "AVG(DATEDIFF(d, ReferralRequestReceivedDate, MIN(A.AppointmentDate))) OVER () AS AvgAllocationWaitTime".)
SELECT
   OA.Name ,
   COUNT(CASE WHEN AppointmentTypeId IN ( 1, 3 )
                   AND AppointmentDate BETWEEN '27 Jan 2013'
                                       AND     '13 Apr 2013'
              THEN AppointmentId
         END) AS AppsBooked ,
   AVG(DATEDIFF(d, ReferralRequestReceivedDate, MIN(A.AppointmentDate))) OVER ( PARTITION BY OA.OrganisationAreaId,
                                                              OA.Name ) AS AvgAllocationWaitTime
FROM
   OrganisationAreas OA
   LEFT OUTER JOIN Clients C
      ON OA.OrganisationAreaId = C.OrganisationAreaId
   LEFT OUTER JOIN IaptReferrals R
      ON C.ClientId = R.ClientId
   LEFT OUTER JOIN IaptAppointments A
      ON R.IaptReferralId = A.Referral_IaptReferralId
GROUP BY
   OA.OrganisationAreaId ,
   OA.NAME
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000