AVG(DATEDIFF(d,table1.date1, MIN(table2.date2))

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

AVG(DATEDIFF(d,table1.date1, MIN(table2.date2))

Author

Topic

moorzee
Starting Member

5 Posts

Posted - 2013-04-22 : 17:50:19

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 18:13:04

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources