AVG(DATEDIFF(d,Tabl1.date1,MIN(Table2.date1))) T-SQL

Я пытаюсь создать отчет для предоставления данных из базы данных SQL Server. У меня есть 3 таблицы, которые меня интересуют: «Клиент», «Реферал», «Назначение». Клиент может иметь 1.* рефералов, а реферал может иметь 0.* назначенных ему встреч.

В своем отчете я хочу показать среднее время от получения направления до первой встречи.

Я пытаюсь сделать это в своей хранимой процедуре, но получаю сообщение «Невозможно выполнить агрегатную функцию для выражения, содержащего агрегат или подзапрос».

Есть ли способ заставить это работать без подзапроса, который вы можете придумать?

Изменить Примерную структуру таблицы ниже.

Клиент ClientID INT IIDENTITY (pk) Имя VARCHAR(50) Фамилия VARCHAR(50) Дата рождения DATETIME

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

Назначение AppintmentID INT IDENTITY (pk) ReferralId INT(fk) AppointmentDate DATETIME AttendanceTypeId INT (fk) AppointmentTypeID INT (fk)

Типы посещаемости AttendanceTypeID INT IDENTITY (pk) Имя VARCHAR(50)

AppointmentTypes AppointmentTypeID INT IDENTITY (pk) Имя VARCHAR(50)

OrganizationArea OrgaisationAreaId INT IDENTITY(pk) Name 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

sql tsql average aggregate-functions
person CheGuevarasBeret    schedule 22.04.2013    source источник
comment
Какова структура этих таблиц? Какие ключи связывают их вместе?   -  person Gordon Linoff    schedule 23.04.2013
comment
почему вы хотите, чтобы эта работа работала без подзапроса?   -  person Ethan Li    schedule 23.04.2013

Ответы (2)


arrow_upward
1
arrow_downward

Я не мог придумать способ без использования подзапроса или 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.Name
person Ethan Li    schedule 23.04.2013
comment
Лучший совет, Итан. Ваше здоровье. Использовал версию CTE и любил ее. - person CheGuevarasBeret; 24.04.2013

arrow_upward
0
arrow_downward

У вас есть это:

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 без условия else. В-третьих, у вас есть кое-что о назначенных встречах, которые кажутся неуместными. Наконец, вы получили сообщение об ошибке из-за неправильного синтаксиса.

Чтобы исправить последнюю ошибку, переместите минимальную дату встречи в подзапрос. Это сделает ваш запрос таким:

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

Сохраняйте эту базовую структуру. Если вы хотите, чтобы число встреч также было подсчитано, постарайтесь сделать его простым.

person Dan Bracuk    schedule 22.04.2013
comment
Привет Дэн. Спасибо за помощь. Я использовал внешние соединения, потому что я все еще хочу, чтобы область возвращалась независимо от того, есть ли какие-либо рефералы, назначенные ей, чтобы я мог отображать нулевые счетчики. Спасибо за помощь людям. Я постараюсь разобраться с функциональностью CTE, выглядит мощно. - person CheGuevarasBeret; 23.04.2013