AVG(DATEDIFF(d，Tabl1.date1，MIN(Table2.date1)
我正在尝试创建一个报表，以从Server数据库中提供数据。我有三个我感兴趣的桌子，客户，推荐人，约会。客户端可以有1.*推荐，而推荐可以有0.*预约。
在我的报告中，我想显示从转诊到第一次预约的平均时间。
在我的存储过程中尝试上述操作，但接收“不能对包含聚合或子查询的表达式执行聚合功能”。
有没有一种不需要子查询就能让它正常工作的巧妙方法？
编辑粗糙表结构如下。
Client ClientID INT IIDENTITY (pk)命名VARCHAR(50)姓VARCHAR(50)道布日期时间
Referral ReferralID INT恒等(pk) ClientID INT (fk) ReferralRequestReceivedDate DATETIME OrgaisationAreaId INT (fk)
AppintmentID INT IDENTITY (pk) ReferralId INT(fk) AppointmentDate DATETIME AttendanceTypeId INT (fk) AppointmentTypeID INT (fk)
AttendanceTypes AttendanceTypeID INT恒等(pk)名称VARCHAR(50)
AppointmentTypes AppointmentTypeID INT恒等(pk)名称VARCHAR(50)
OrganisationArea OrgaisationAreaId INT恒等(Pk)名称VARCHAR(50)
我现有的程序按年龄和出勤类型计算的预约次数如下……
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回答 2
Stack Overflow用户
发布于 2013-04-23 05:43:43
我想不出一种不用子查询或cte的方法。但是，如果您对子查询没有问题，请尝试如下
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, sq.MinAppointmentDate)) AS AvgAllocationWaitTime
FROM OrganisationAreas OA
LEFT OUTER JOIN
(
SELECT C.OrganisationAreaId, MIN(A.AppointmentDate) MinAppointmentDate
FROM IaptAppointments A
LEFT OUTER JOIN IaptReferrals R ON R.IaptReferralId = A.Referral_IaptReferralId
LEFT OUTER JOIN Clients C ON C.ClientId = R.ClientId
GROUP BY C.OrganisationAreaId
) sq ON sq.OrganisationAreaId = OA.OrganisationAreaId
GROUP BY OA.OrganisationAreaId, OA.Name或cte版本：
;WITH cte AS
(
SELECT C.OrganisationAreaId, MIN(A.AppointmentDate) MinAppointmentDate
FROM IaptAppointments A
LEFT OUTER JOIN IaptReferrals R ON R.IaptReferralId = A.Referral_IaptReferralId
LEFT OUTER JOIN Clients C ON C.ClientId = R.ClientId
GROUP BY C.OrganisationAreaId
)
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, cte.MinAppointmentDate)) AS AvgAllocationWaitTime
FROM OrganisationAreas OA
LEFT OUTER JOIN cte ON cte.OrganisationAreaId = OA.OrganisationAreaId
GROUP BY OA.OrganisationAreaId, OA.NameStack Overflow用户
发布于 2013-04-22 22:58:16
你有这个：
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让我们看看如何改进它。首先，如果希望从推荐日期到第一次约会之间的平均时间，则需要内部连接，而不是外部连接。其次，您有一个没有其他条件的case结构。第三，你对预定的约会有一些似乎无关紧要的东西。最后，您得到了错误语法的错误消息。
若要修复最后一个错误，请将min约会日期移到子查询中。这将使您的查询看起来如下：
select oa.name
, avg(datediff(d, ReferralRequestReceivedDate, MinAppointmentDate))
AvgAllocationWaitTime
from OrganisationAreas oa
JOIN Clients C
ON OA.OrganisationAreaId = C.OrganisationAreaId
JOIN IaptReferrals R
ON C.ClientId = R.ClientId
join (
select referral_iaptReferralId refid
, min(appointmentdate) MinAppointmentDate
from IaptAppointments
where clause goes here
group by referral_iaptReferralId
) temp on refid = r.iaptReferralId
where clause goes here保持这个基本结构。如果你也想要一个约会计数，试着保持简单。
https://stackoverflow.com/questions/16155694
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号