CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alex Gordon
Alex Gordon

Reputation: 60711

How can I get the average difference between the max and min dates?

I am trying to get the average number of months difference between two dates:

select client_id, 
        avg(12*(year(MAX(received_date))-year(min(received_date))) 
        + MONTH(MAX(received_date))-MONTH(min(received_date)))
        from tmpTwoAccessions
        group by CLIENT_ID,PATIENT_ID

I am getting this message:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Can you please advise me on what am I doing incorrectly here? Thanks so much for your guidance.

Upvotes: 0

Views: 2238

Answers (3)

Farfarak
Farfarak

Reputation: 1517

Would be cleaner to implement with DATEDIFF.

WITH tmpTwoAccessionsMonths AS(         
    select 
          client_id
        , PATIENT_ID
        , DATEDIFF(month,MIN(received_date),MAX(received_date)) MonthDifference
    from 
        tmpTwoAccessions
    group by 
        CLIENT_ID,PATIENT_ID
)
SELECT
    client_id
    ,AVG(MonthDifference)
FROM
    tmpTwoAccessionsMonths
GROUP BY
    client_id

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 753665

You do the difference between the MAX and MIN in a sub-query, and then average that in the outer query.

SELECT client_id, AVG(delta_date)
  FROM (SELECT client_id, patient_id,
               12 * (YEAR(MAX(received_date))-YEAR(MIN(received_date))) 
               + MONTH(MAX(received_date)) - MONTH(MIN(received_date))) AS delta_date
          FROM tmpTwoAccessions
         GROUP BY client_id, patient_id
       ) AS x
 GROUP BY client_id;

I'm not convinced your date difference expression is optimal, but I've not tried changing it.

Yurly Rozhovetskiy suggested the expression:

DATEDIFF(MONTH, MIN(received_date), MAX(received_date))

which looks good to me. The revised query becomes:

SELECT client_id, AVG(delta_date)
  FROM (SELECT client_id, patient_id,
               DATEDIFF(MONTH, MIN(received_date), MAX(received_date)) AS delta_date
          FROM tmpTwoAccessions
         GROUP BY client_id, patient_id
       ) AS x
 GROUP BY client_id;

Upvotes: 2

user848765
user848765

Reputation:

Try using a subquery like this:

select
    T.client_id
    ,avg(T.ReceivedDateDiff)
from 
(
    select client_id, 
    (12*(year(MAX(received_date))-year(min(received_date))) 
        + MONTH(MAX(received_date))-MONTH(min(received_date))) AS ReceivedDateDiff
    from tmpTwoAccessions
    group by CLIENT_ID,PATIENT_ID
)
T
GROUP BY client_id

Upvotes: 3

Related Questions